Salta el contingut

DDL - Creació de Taules i Restriccions

Restriccionss

Tota taula té restriccions que poden posar en el moment de la creació de la taula o bé posteriorment amb la comanda ALTER TABLE.

Aquestes restriccions són: PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY, CHECK.

Sempre és bo que les restriccions tinguin un nom que les identifiqui, tot i que MariaDB posi de nom PRIMARY a totes les resticcions de clau primària.

Les restriccions de NOT NULL no solen tenir nom ja que les posem directament al CREATE TABLE.

Restriccions en el nivell de columna

Hi ha restriccions que fan referència a una sola columna i podem posar-les a la definició de la columna en la instrucció CREATE TABLE.

Les definim a la definició de la columna o al final de tot.

CREATE TABLE T2_1 (
    ID_T2 INT(4) PRIMARY KEY,
    D_T2 VARCHAR(25) NOT NULL
);
CREATE TABLE T2_2 (
    ID_T2 INT(4),
    D_T2 VARCHAR(25),
    CONSTRAINT PK_T1 PRIMARY KEY (ID_T2),
    CONSTRAINT T2_2_D_T2_NN CHECK (D_T2 IS NOT NULL)
);

Restriccions en el nivell de taula

Les restriccions que afecten a més d’un camp a la vegada cal definir-los per separat en la comanda CREATE TABLE igual que les restriccions de clau forana.

El més normal és definir-les al final de tot juntament amb altres restriccions, si cal.

Exemple
CREATE TABLE T3 (
    ID_T3 CHAR(2),
    ID_T2 INT(4),
    D_T3 VARCHAR(25),
    CONSTRAINT PK_T3 PRIMARY KEY (ID_T3, ID_T2),
    CONSTRAINT FK_T3_T2_1 FOREIGN KEY (ID_T2)
               REFERENCES T2_1(ID_T2)
);

Nomenclatura de les restriccions

Tota restricció té un nom i per poder identificar-la és bo utilitzar un nom que no es pugui repetir i les identifiqui: el tipus de restricció, el nom de la taula i el camp o camps o taula que hi participen.

Per les restriccions de NOT NULL no utilitzarem el CHECK ja que la informació es guarda a la mateixa definició de la taula.

SELECT *
FROM information_schema.table_constraints
WHERE table_name IN ('T2_1', 'T2_2', 'T3');

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name IN ('T2_1', 'T2_2', 'T3')
ORDER BY table_name, ordinal_position;

Diccionari de Dades

La base de dades information_schema emmagatzema les metadades on podem consultar informació de les taules, restriccions, etc.

Per les restriccions consultarem les taules de information_schema (qualsevol usuari):

  • TABLE_CONSTRAINTS

  • CHECK_CONSTRAINTS

  • REFERENTIAL_CONSTRAINTS

  • KEY_COLUMN_USAGE

Algunes taules només són accessibles per l’usuari root.

  • INNODB_SYS_FOREIGN

  • INNODB_SYS_FOREIGN_COLS

Per veure les restriccions podem consultar totes aquestes taules del diccionari de dades.

NOT NULL - UNIQUE

Una columna definida com a NOT NULL necessita sempre un valor per cada fila.

Una restricció UNIQUE necessita que tots els valors d’una o vàries columnes siguin diferents; és a dir, dues files diferents no poden tenir el mateix valor o combinació de valors. Aquest camp o camps, podem definir-los NULL si ens interessa.

Si la combinació de dues o més columnes ha de ser única per a cada entrada, es diu que la restricció és una clau única composta.

Exemple de UNIQUE

La columna email de la taula EMPLOYEES està definida com única i per tant no pot repetir-se.

En cas d’entrar un valor duplicat es produirà un error del tipus Duplicate entry.

ERROR 1062 (23000): Duplicate entry 'VALOR' for key 'EMP_EMAIL_UK'

Definició de UNIQUE

Al definir una restricció UNIQUE, si volem posar-li nom, podem utilitzar el prefix o sufix uk. Per exemple EMP_EMAIL_UK.

Per a definir una clau única composta, cal fer-ho en el nivell de taula de diferents formes.

CREATE TABLE T4 (
    C1 INT NOT NULL UNIQUE,
    C2 INT NULL UNIQUE,
    C3 INT UNIQUE KEY,
    C4 INT,
    C5 INT NOT NULL,
    C6 INT NOT NULL,
    UNIQUE KEY T4_C4_UK (C4),
    CONSTRAINT T4_C5_UK UNIQUE (C5, C6)
);

SHOW CREATE TABLE

Per saber com recrear una taula podem utilitzar la comanda SHOW CREATE TABLE.

SHOW CREATE TABLE T4;

CREATE TABLE `t4` (
    `C1` int(11) NOT NULL,
    `C2` int(11) DEFAULT NULL,
    `C3` int(11) DEFAULT NULL,
    `C4` int(11) DEFAULT NULL,
    `C5` int(11) NOT NULL,
    `C6` int(11) NOT NULL,
    UNIQUE KEY `C1` (`C1`),
    UNIQUE KEY `T4_C5_UK` (`C5`,`C6`),
    UNIQUE KEY `C2` (`C2`),
    UNIQUE KEY `C3` (`C3`),
    UNIQUE KEY `T4_C4_UK` (`C4`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1