Chiavi esterne

Panoramica

Le chiavi esterne (foreign key) sono vincoli che garantiscono l'integrità dei dati. Sono composte da una colonna (o un insieme di colonne) in una tabella, chiamata tabella figlia, che si riferisce a una colonna (o un insieme di colonne) in una tabella chiamata tabella madre. Se si utilizzano le chiavi esterne, MariaDB effettua i necessari controlli per garantire che alcune regole vengano rispettate.

Le chiavi esterne possono essere utilizzate solo con gli storage engine che le supportano. InnoDB e l'obsoleto PBXT le supportano.

Sintassi

Le chiavi esterne possono essere create con CREATE TABLE o ALTER TABLE. La definizione deve seguire la seguente sintassi:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

La clausola symbol, se presente, viene utilizzata nei messaggi di errore e deve essere univoca all'interno del database.

Le colonne nella tabella figlia devono costituire un indice, o la parte iniziale di un indice. I prefissi degli indici non sono supportati (quindi, le colonne TEXT e BLOB non possono essere usate come chiavi esterne). Se MariaDB crea automaticamente un indice per la chiave esterna (perché questo non viene creato esplicitamente dall'utente), il suo nome sarà index_name.

Le colonne referenziate da una chiave primaria devono essere una chiave primaria o un indice UNIQUE.

Sia la chiave esterna, sia le colonne referenziate, possono essere colonne PERSISTENT.

Le colonne della chiave esterna e le colonne a cui queste fanno riferimento devono essere dello stesso tipo, o di tipi simili. Per i tipi interi, le dimensioni e il segno devono essere identici.

La tabella madre e la tabella figlia devono utilizzare lo stesso storage engine, e non devono essere temporanee o partizionate. Possono essere la stessa tabella.

Vincoli

Se una chiave esterna esiste, ogni riga nella tabella figlia deve corrispondere a una riga nella tabella madre. Più righe nella tabella figlia possono corrispondere alla stessa riga nella tabella madre. Una riga figlia corrisponde a una riga madre se tutti i valori della chiave esterna sono identici ai valori della riga nella tabella madre. Tuttavia, se almeno uno dei valori della chiave esterna è NULL, la riga non ha madri, ma è ammessa.

MariaDB effettua alcuni controlli per garantire l'integrità dei dati:

• Cercare di inserire righe senza corrispondenza nella tabella figlia (o modificare le righe esistenti in modo che non trovino più corrispondenze) produce un errore 1452 (SQLSTATE '23000').
• Quando una riga nella tabella madre viene eliminata e almeno una riga figlia esiste, MariaDB effettua un'azione che dipende dalla clausola ON DELETE della chiave esterna.
• Quando una riga nella tabella madre cambia e almeno una riga figlia esiste, MariaDB effettua un'azione che dipende dalla clausola ON UPDATE della chiave esterna.
• Cercare di eliminare una tabella referenziata da una chiave esterna produce un errore 1217 (SQLSTATE '23000').

Le azioni ammesse per ON DELETE e ON UPDATE sono:

• RESTRICT: La modifica sulla tabella madre viene impedita. L'istruzione termina con un errore 1451 (SQLSTATE '2300'). Questo è il comportamento predefinito per ON DELETE e ON UPDATE.
• NO ACTION: Sinonimo di RESTRICT.
• CASCADE: La modifica è permessa e si propaga sulla tabella figlia. Per esempio, se si elimina una riga madre, viene eliminata anche la riga figlia; se l'ID della riga madre cambia, cambierà anche l'ID della riga figlia.
• SET NULL: La modifica è permessa e i valori della chiave esterna della riga figlia vengono impostati a NULL.
• SET DEFAULT: Funzionava solo con PBXT. E' simile a SET NULL, ma le colonne della chiave esterna vengono impostate ai loro valori predefiniti. Se queste non hanno valori predefiniti, viene generato un errore.

I vincoli delle chiavi esterne possono essere disabilitati impostando la variabile foreign_key_checks a 0. Questo velocizza l'inserimento di grandi quantità di dati.

Metadati

La tabella Information Schema REFERENTIAL_CONSTRAINTS contiene informazioni sulle chiavi esterne. Le singole colonne sono elencate nella tabella KEY_COLUMN_USAGE.

Anche le tabelle dell'Information Schema specifiche di InnoDB contengono informazioni sulle chiavi esterne di InnoDB. Le informazioni sulle chiavi esterne si trovano nella tabella INNODB_SYS_FOREIGN. I dati sulle singole colonne si trovano in INNODB_SYS_FOREIGN_COLS.

A volte il modo più leggibile per ottenere informazioni sulle chiavi esterne di una tabella è l'istruzione SHOW CREATE TABLE.

Esempi

Ma vediamo un esempio. Creeremo una tabella author e una tabella book. Entrambe hanno una chiave primaria chiamata id. book ha anche una chiave esterna composta da una colonna chiamata author_id, che si riferisce alla chiave primaria di author. Il nome del vincolo della chiave esterna è opzionale, ma lo specificheremo perché apparirà nei messaggi di errore: fk_book_author.

CREATE TABLE author (
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
	id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(200) NOT NULL,
	author_id SMALLINT UNSIGNED NOT NULL,
	CONSTRAINT `fk_book_author`
		FOREIGN KEY (author_id) REFERENCES author (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
) ENGINE = InnoDB;

Ora, se cerchiamo di inserire un libro di un autore inesistente, otterremo un errore:

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE CASCADE)

L'errore è molto chiaro.

Proviamo a inserire in modo appropriato due autori e i loro libri:

INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());

INSERT INTO author (name) VALUES ('H.P. Lovecraft');
INSERT INTO book (title, author_id) VALUES
	('The call of Cthulhu', LAST_INSERT_ID()),
	('The colour out of space', LAST_INSERT_ID());

Ha funzionato!

Ora, cancelliamo il secondo autore. Nel creare la chiave esterna, abbiamo specificato ON DELETE CASCADE. Ciò dovrebbe propagare la cancellazione, e fare sparire i libri dell'autore eliminato:

DELETE FROM author WHERE id = 2;

SELECT * FROM book;
+----+--------------+-----------+
| id | title        | author_id |
+----+--------------+-----------+
|  3 | Necronomicon |         1 |
+----+--------------+-----------+

Abbiamo specificato anche ON UPDATE RESTRICT. Questo dovrebbe impedire la modifica dell'id di un autore (la colonna a cui si riferisce la chiave esterna) se una riga figlia esiste:

UPDATE author SET id = 10 WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE CASCADE)