Diferència entre revisions de la pàgina «ASIX/M02/UF2/A08/E2»

De Lordwektabyte Wiki
Salta a la navegació Salta a la cerca
Línia 1: Línia 1:
 +
[[Category:M02]]
 
{{Titol|Pt82-Pràctica de creació, modificació i eliminació de taules}}
 
{{Titol|Pt82-Pràctica de creació, modificació i eliminació de taules}}
 
{{Capcalera|Guillem Solà i Boeck|05}}
 
{{Capcalera|Guillem Solà i Boeck|05}}

Revisió del 17:40, 7 maig 2018


Guillem Solà i Boeck (host A10BPC05)

Sobre la base de dades Documents, fer les preguntes:

Primer de tot, canviem la BD en ús per a la que hem creat: 

MariaDB [(none)]> use documents;
Database changed
MariaDB [documents]>

Creació de taules

Creo la taula Pdf: 

MariaDB [documents]> CREATE TABLE IF NOT EXISTS Pdf ( 
    -> idPdf varchar(5) PRIMARY KEY, 
    -> Nom varchar(25) UNIQUE);
Query OK, 0 rows affected (0.36 sec)

Creo la taula Tema: 

MariaDB [documents]> CREATE TABLE IF NOT EXISTS Tema (  
idTema varchar(5) PRIMARY KEY,  
Descripcio varchar(25) UNIQUE, 
SubTema varchar(5));
Query OK, 0 rows affected (0.29 sec)

Creo la taula Document: 

MariaDB [documents]> CREATE TABLE IF NOT EXISTS Document (  
idDoc varchar(5) PRIMARY KEY,  
Nom varchar(25) UNIQUE, 
Tipus varchar(15));
Query OK, 0 rows affected (0.31 sec)

Creo la taula TemaDoc: 

MariaDB [documents]> CREATE TABLE IF NOT EXISTS TemaDoc ( 
    -> idTema varchar(5),
    -> idDoc varchar(5),
    -> PRIMARY KEY (idTema,idDoc)
    -> );
Query OK, 0 rows affected (0.28 sec)

Comprovo la creació: 

MariaDB [documents]> show tables;
+---------------------+
| Tables_in_documents |
+---------------------+
| Document            |
| Pdf                 |
| Tema                |
| TemaDoc             |
+---------------------+
4 rows in set (0.00 sec)


Modificació de taules

Llavors afegeixo les FKs: 

MariaDB [documents]> ALTER TABLE TemaDoc ADD CONSTRAINT FOREIGN KEY (idTema) REFERENCES Tema (idTema) ON UPDATE CASCADE ON DELETE RESTRICT;
Query OK, 0 rows affected (0.68 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [documents]> ALTER TABLE TemaDoc ADD CONSTRAINT FOREIGN KEY (idDoc) REFERENCES Document (idDoc) ON UPDATE CASCADE ON DELETE RESTRICT;
Query OK, 0 rows affected (0.87 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [documents]> ALTER TABLE Tema ADD CONSTRAINT FOREIGN KEY (SubTema) REFERENCES Tema (idTema) ON UPDATE CASCADE ON DELETE RESTRICT;
Query OK, 0 rows affected (0.95 sec)               
Records: 0  Duplicates: 0  Warnings: 0

Eliminació de taules

MariaDB [documents]> DROP TABLE Pdf;
Query OK, 0 rows affected (0.12 sec)

MariaDB [documents]> SHOW TABLES;
+---------------------+
| Tables_in_documents |
+---------------------+
| Document            |
| Tema                |
| TemaDoc             |
+---------------------+
3 rows in set (0.00 sec)


Comprovació dels SHOW CREATE TABLES

Taula Document: 

MariaDB [documents]> show create table Document;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                              |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Document | CREATE TABLE `Document` (
  `idDoc` varchar(5) NOT NULL,
  `Nom` varchar(25) DEFAULT NULL,
  `Tipus` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`idDoc`),
  UNIQUE KEY `Nom` (`Nom`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Taula Tema: 

MariaDB [documents]> show create table Tema;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Tema  | CREATE TABLE `Tema` (
  `idTema` varchar(5) NOT NULL,
  `Descripcio` varchar(25) DEFAULT NULL,
  `SubTema` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`idTema`),
  UNIQUE KEY `Descripcio` (`Descripcio`),
  KEY `SubTema` (`SubTema`),
  CONSTRAINT `Tema_ibfk_1` FOREIGN KEY (`SubTema`) REFERENCES `Tema` (`idTema`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Taula TemaDoc: 

MariaDB [documents]> show create table TemaDoc;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                     |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TemaDoc | CREATE TABLE `TemaDoc` (
  `idTema` varchar(5) NOT NULL,
  `idDoc` varchar(5) NOT NULL,
  PRIMARY KEY (`idTema`,`idDoc`),
  KEY `idDoc` (`idDoc`),
  CONSTRAINT `TemaDoc_ibfk_1` FOREIGN KEY (`idTema`) REFERENCES `Tema` (`idTema`),
  CONSTRAINT `TemaDoc_ibfk_2` FOREIGN KEY (`idDoc`) REFERENCES `Document` (`idDoc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Obtingut de «http://wiki.lordwektabyte.cat/index.php?title=ASIX/M02/UF2/A08/E2&oldid=790»