A foreign key is a constraint which can be used to enforce data integrity. It is composed by a column (or a set of columns) in a table called the child table, which references to a column (or a set of columns) in a table called the parent table. If foreign keys are used, MariaDB performs some checks to enforce that some integrity rules are always enforced.
[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
symbol clause, if specified, is used in error messages and must be unique in the database.
The columns in the child table must be an index, or the leftmost part of an index. Index prefixes are not supported (thus, TEXT and BLOB columns cannot be used as foreign keys). If MariaDB automatically creates an index for the foreign key (because it does not exist and is not explicitly created), its name will be
The referenced columns must be a PRIMARY KEY or a UNIQUE index.
Both the foreign keys and the referenced columns can be PERSISTENT columns.
The foreign key columns and the referenced columns must be of the same type, or similar types. For integer types, the size and sign must also be the same.
The parent and the child table must use the same storage engine, and must not be
TEMPORARY or partitioned tables. They can be the same table.
If a foreign keys exists, each row in the child table must match a row in the parent table. Multiple child row can match the same parent row. A child row matches a parent row if all its foreign key values are identical to a parent row's values in the parent table. However, if at least one of the foreign key values is
NULL, the row has no parents, but it is still allowed.
MariaDB performs certain checks to guarantee that the data integrity is enforced:
- Trying to insert non-matching rows (or update matching rows in a way that makes them non-matching rows) in the child table produces a 1452 error (SQLSTATE '23000').
- When a row in the parent table is deleted and at least one child row exists, MariaDB performs an action which depends on the
ON DELETEclause of the foreign key.
- When a value in the column referenced by a foreign key changes and at least one child row exists, MariaDB performs an action which depends on the
ON UPDATEclause of the foreign key.
- Trying to drop a table that is referenced by a foreign key produces a 1217 error (SQLSTATE '23000').
The allowed actions for
ON DELETE and
ON UPDATE are:
RESTRICT: The change on the parent table is prevented. The statement terminates with a 1451 error (SQLSTATE '2300'). This is the default behavior for both
NO ACTION: Synonym for
CASCADE: The change is allowed and propagates on the child table. For example, if a parent row is deleted, the child row is also deleted; if a parent row's ID changes, the child row's ID will also change.
SET NULL: The change is allowed, and the parent row's foreign key columns are set to
SET DEFAULT: Only worked with PBXT. Similar to
SET NULL, but the foreign key columns were set to their default values. If default values do not exist, an error is produced.
Foreign key constraints can be disabled by setting the foreign_key_checks server system variable to 0. This speeds up the insertion of large quantities of data.
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
The most human-readable way to get information about a table's foreign keys sometimes is the
SHOW CREATE TABLE statement.
Let's see an example. We will create an
author table and a
book table. Both tables have a primary key called
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:
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());
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)