DDL - Creació de Taules i Restriccions

PRIMARY KEY

Una restricció PRIMARY KEY és una regla que indica que els valors d'una o vàries columnes han d'identificar de forma única cadascuna de les files d'una taula.

Per complir una restricció de Clau primària cal donar-se:

• Cap columna que forma part de la Clau primària pot contenir un valor NULL,

• La clau primària ha de ser única.

Una taula no pot tenir definida més d'una clau primària.

La PRIMARY KEY podem definir-la a nivell de camp, quan està formada per un sol camp, o a nivell de taula en qualsevol cas.

En MariaDB, tot i que li posem un alter nom, la restricció de clau primària sempre s'anomena PRIMARY.

CREATE TABLE T5 (
    C1 INT PRIMARY KEY, ...
);
CREATE TABLE T6 (
    C1 INT, C2 INT, ... ,
    CONSTRAINT PK_T6 PRIMARY KEY(C1, C2)
);

AUTO_INCREMENT

Sovint una clau primària volem que sigui una seqüència de números sense importar-nos el seu valor sinó només que siguin nombres que no es repeteixin.

En MariaDB tenim l'atribut AUTO_INCREMENT que s'utilitza per a generar identificadors únics per a files noves.

Quan inserim un registre nou a una taula i el valor del camp AUTO_INCREMENT és !sql NULL o DEFAULT, el valor s'incrementarà automàticament.

Les columnes AUTO_INCREMENT per defecte s'inicien amb el valor 1 i l'increment és d'1 en 1.

Cada taula pot tenir només una columna AUTO_INCREMENT i s'ha de definir com a KEY.

Podem modificar el valor inicial amb la comanda ALTER TABLE.

Per saber el valor que ha generat un INSERT podem utilitzar la funció LAST_INSERT_ID() per a consultar-ho.

Exemple d'AUTO_INCREMENT i de LAST_INSERT_ID()

CREATE TABLE T7 (
    id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    f VARCHAR(1));
INSERT INTO T7(f) VALUES('a');
SELECT LAST_INSERT_ID(); -- Torna 1
INSERT INTO T7 VALUES(NULL, 'b');
INSERT INTO T7 VALUES(DEFAULT, 'c');
SELECT LAST_INSERT_ID(); -- Torna 3
INSERT INTO T7(f) VALUES('d'),('e');
SELECT LAST_INSERT_ID(); -- Torna 4

SELECT * FROM T7;
+----+------+
| id | f    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+

FOREIGN KEY

Una restricció PRIMARY KEY és una regla que indica que els valors d'una o vàries columnes han d'identificar de forma única cadascuna de les files d'una taula.

Aquesta restricció designa un o més camps com a Clau Forana.

Una Clau Forana enllaça a una clau primària en una altra, o la mateixa, taula. Aquest enllaç és la base de les relacions entre taules.

La taula que conté la clau primària s'anomena taula principal mentre que la que conté la clau forana l'anomenem taula secundària.

Nosaltres hem treballat amb vàries d'aquestes relacions.

• DEPARTMENTS – EMPLOYEES,

• JOBS – EMPLOYEES,

• REGIONS – COUNTRIES – LOCATIONS – DEPARTMENTS.

RESTRICCIONS D’INTEGRITAT REFERENCIAL

Per complir una restricció d’integritat referencial, el valor de la clau forana ha d’existir en alguna fila de la taula principal o bé ser NULL.

Poden tenir claus primàries sense que tinguin cap clau forana apuntant a elles, però no pot existir cap clau forana que no referenciï a una clau primària.

Cal definir la restricció de la taula primària, com UNIQUE o PRIMARY, abans que la de la taula secundària com a FOREIGN KEY.

Solem utilitzar el prefix o sufix FK

Exemple de FOREIGN KEY

CREATE TABLE T8 (
    ID_1 INT AUTO_INCREMENT PRIMARY KEY,
    ID_2 CHAR(2),
    ID_3 INT,
    CONSTRAINT T8_ID_2_ID_3_UU UNIQUE(ID_2, ID_3)
);
CREATE TABLE T9 (
    ID_1 INT PRIMARY KEY,
    ID_2 CHAR(2),
    ID_3 INT,
    CONSTRAINT T9_T8_ID_1 FOREIGN KEY (ID_1)
               REFERENCES T8(ID_1),
    CONSTRAINT T9_T8_ID_2_ID_3 FOREIGN KEY (ID_2, ID_3)
               REFERENCES T8(ID_2, ID_3)
);

Manteniment de les Restriccions d'Integritat

Què passa si en algun moment volem eliminar una fila d’una taula principal o modificar el valor d’una columna, referenciada per una clau forana? Quedarà la fila de la taula secundària òrfena?

Quan creem una restricció de clau fornana tenim la opció de determinar el comportament en el cas explicat anteriorment.

El comportament a definir pot ser diferent en cas d’eliminació que en cas d’actualització.

Les opcions són: ON DELETE i ON UPDATE

I en cada cas podem determinar:

• RESTRICT: S'evita el canvi a la taula pare. La instrucció acaba amb un error 1451 ( SQLSTATE '2300'). Aquest és el comportament predeterminat per a tots dos ON DELETE i ON UPDATE.

• NO ACTION: Sinònim de RESTRICT

• CASCADE: El canvi es permet i es propaga a la taula fill. Per exemple, si s'elimina una fila principal, també se suprimirà la fila secundària; si l'identificador d'una fila principal canvia, també canviarà l'identificador de la fila secundària

• SET NULL: el canvi està permès i les columnes de clau estrangera de la fila secundària s'estableixen en NULL, sempre que es pugui.

• SET DEFAULT: només funcionava amb PBXT. Similar a SET NULL, però a les columnes de clau forana se’ls assignen els seus valors default. Si no existeixen valors per defecte, es produeix un error.

Exemple de FOREIGN KEY

DROP TABLE T9;
CREATE TABLE T9 (
    ID_1 INT PRIMARY KEY,
    ID_2 CHAR(2),
    ID_3 INT,
    CONSTRAINT T9_T8_ID_1 FOREIGN KEY (ID_1)
               REFERENCES T8(ID_1)
               ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT T9_T8_ID_2_ID_3 FOREIGN KEY (ID_2, ID_3)
               REFERENCES T8(ID_2, ID_3)
               ON UPDATE CASCADE ON DELETE SET NULL
);

Metadades de les claus foranes

Per trobar informació sobre les claus foranes cal consultar les taules de la base information_schema:

• REFERENTIAL_CONSTRAINTS

• KEY_COLUMN_USAGE

• INNODB_SYS_FOREIGN

• INNODB_SYS_FOREIGN_COLS

CHECK

Una restricció CHECK defineix explícitament una condició que cal complir.

Per tal que es compleixi la condició cal que aquesta s'avaluï com a TRUE o com a NULL.

La condició pot fer referència a qualssevol camps de la taula però no a camps d’altres taules.

Una comprovació pot ser que un camp ha de ser positiu (també podem definir el camp com UNSIGNED)

O validar que una data és sempre posterior a una altra.

CREATE TABLE T10 (
    ID_1 INT PRIMARY KEY,
    SOU DECIMAL(10,2) CHECK ( SOU > 0 ),
    DATA_INICI DATE NOT NULL,
    DATA_FI DATE NULL,
    CONSTRAINT T10_DATES_CK CHECK (DATA_INICI < DATA_FI)
);

La sintaxis de les restriccions CHECK és la que hem pogut veure a l’exemple anterior:

• A nivell de columna

  <nom_del_camp> <definició_del_camp> CHECK ( <condició> )
  

• A nivell de taula

  CONSTRAINT <nom_restricció> CHECK ( <condició> )