Foreign Keys

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 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.

Metadata

The Information Schema REFERENTIAL_CONSTRAINTS table contains information about foreign keys. The individual columns are listed in the KEY_COLUMN_USAGE table.

The InnoDB-specific Information Schema tables also contain information about the InnoDB foreign keys. The foreign key information is stored in the INNODB_SYS_FOREIGN. Data about the individual columns are stored in INNODB_SYS_FOREIGN_COLS.

The most human-readable way to get information about a table's foreign keys sometimes is the SHOW CREATE TABLE statement.

Examples

Let's see an example. We will create an author table and a book table. Both tables have a primary key called id. book also has a foreign key composed by a field called author_id, which refers to the author primary key. The foreign key constraint name is optional, but we'll specify it because we want it to appear in error messages: 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;

Now, if we try to insert a book with a non-existing author, we will get an error:

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)

The error is very descriptive.

Now, let's try to properly insert two authors and their books:

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());

It worked!

Now, let's delete the second author. When we created the foreign key, we specified ON DELETE CASCADE. This should propagate the deletion, and make the deleted author's books disappear:

DELETE FROM author WHERE id = 2;

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

We also specified ON UPDATE RESTRICT. This should prevent us from modifying an author's id (the column referenced by the foreign key) if a child row exists:

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)