Salta el contingut

DDL - Creació de Taules i Restriccions

Aquest document ha de mostrar les diferents opcions de la comanda ALTER TABLE per a poder afegir i eliminar restriccions.

Sempre és molt millor consultar el manual online per a veure’n totes les possibilitats.

Les paraules claus per buscar en la comanda ALTER TABLE son: ADD, DROP, ...

ALTER TABLE – ADD

Per afegir restriccions utilitzarem la comanda ALTER TABLE amb la següent sintaxis:

ALTER TABLE <taula>
    ADD CONSTRAINT <constraint_name> CHECK (<expressió>);

ALTER TABLE <taula>
    ADD CONSTRAINT <constraint_name>
        PRIMARY KEY ( <nom_columna1>, <nom_columna2>, ...);

ALTER TABLE <taula>
    ADD CONSTRAINT <constraint_name>
        FOREIGN KEY ( <nom_columna1>, <nom_columna2>, ...)
        REFERENCES <taula_p> ( <nom_col1>, <nom_col2>, ...);

ALTER TABLE <taula>
    ADD CONSTRAINT <constraint_name> UNIQUE(<col1>, <col2>, );

ALTER TABLE – DROP

Per eliminar restriccions utilitzarem la comanda ALTER TABLE amb la següent sintaxis; sovint és important conèixer el nom de la restricció.

ALTER TABLE <nom_taula>
    DROP CONSTRAINT [IF EXISTS] <nom_restricció>;
ALTER TABLE <nom_taula>
    DROP PRIMARY KEY;
ALTER TABLE <nom_taula>
    DROP {INDEX|KEY} [IF EXISTS] <nom_index>;
ALTER TABLE <nom_taula>
    DROP FOREIGN KEY [IF EXISTS] <nom_restricció>;

La primera opció és sempre vàlida.

Consultes al diccionari - CHECK

Podem consultar els CHECKs d’una base o taula en la taula CHECK_CONSTRAINTS de la base information_schema.

SELECT TABLE_NAME, CONSTRAINT_NAME, CHECK_CLAUSE
FROM CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'test';
+-------+------------+-----------------------------------------+
|taula  |constraint_n|check_clause                             |
+-------+------------+-----------------------------------------+
|adreces|CODI_POSTAL |`CODI_POSTAL` between '01000' and '52999'|
|t1     |C4          |`C4` > 0                                 |
|t10    |SOU         |`SOU` > 0                                |
|t10    |T10_DATES_CK|`DATA_INICI` < `DATA_FI`                 |
|t2_2   |T2_2_D_T2_NN|`D_T2` is not null                       |
+-------+------------+-----------------------------------------+

Consultes al diccionari - REFERENCES

Podem consultar la taula REFERENTIAL_CONSTRAINTS per trobar les PKs i les FKs.

SELECT TABLE_NAME, REFERENCED_TABLE_NAME,
       CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE
FROM REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'TEST';
+---------+-----------+-------------------+----------+----------+
| taula   | taula_ref | CONSTRAINT_NAME   | Update   | Delete   |
+---------+-----------+-------------------+----------+----------+
| adreces | paisos    | FK_ADRECES_PAISOS | RESTRICT | RESTRICT |
| t3      | t2_1      | FK_T3_T2_1        | RESTRICT | RESTRICT |
| t9      | t8        | T9_T8_ID_1        | RESTRICT | RESTRICT |
| t9      | t8        | T9_T8_ID_2_ID_3   | CASCADE  | SET NULL |
+---------+-----------+-------------------+----------+----------+

I les columnes afectades a la taula KEY_COLUMN_USAGE

SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, ORDINAL_POSITION
FROM KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = 'TEST'
ORDER BY 1,2,4;
+---------+-------------------+----------+---+
| t4      | C1                | C1       | 1 |
| t4      | C2                | C2       | 1 |
| t4      | C3                | C3       | 1 |
| adreces | FK_ADRECES_PAISOS | ID_PAIS  | 1 |
| t3      | FK_T3_T2_1        | ID_T2    | 1 |
| t3      | PRIMARY           | ID_T3    | 1 |
| t3      | PRIMARY           | ID_T2    | 2 |
| t4      | T4_C5_UK          | C5       | 1 |
| t4      | T4_C5_UK          | C6       | 2 |
| t8      | T8_ID_2_ID_3_UU   | ID_2     | 1 |
| t8      | T8_ID_2_ID_3_UU   | ID_3     | 2 |

Diccionari de dades

Recorda que tens el diccionari de dades a la base information_schema i que les taules afectades solen ser:

Us animem a investigar més taules del diccionari de dades, show tables, a la base information_schema o mysqlsi sou root.