Foreign Keys

Complete MariaDB performance optimization guide. Complete reference for query tuning, indexing strategies, and configuration improvements for production use.

Overview

A foreign key is a constraint which can be used to enforce data integrity. It is composed of 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. With foreign keys, MariaDB performs checks to enforce that integrity rules are always enforced. For a more exhaustive explanation, see Relational databases: Foreign Keys.

Foreign keys can only be used with storage engines that support them. The default InnoDB supports foreign keys.

Partitioned tables cannot contain foreign keys, and cannot be referenced by a foreign key.

Syntax

CREATE TABLE b(for_key INT REFERENCES a(not_key));

MariaDB applies the constraint if possible. See the Examples below.

Foreign keys are created with CREATE TABLE or ALTER TABLE. The foreign key definition has this syntax:

[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

The symbol clause is used in error messages and must be unique per database, or, as of MariaDB 12.1, unique per table.

The symbol clause is optional. If it isn't specified, MariaDB automatically assigns one.

If MariaDB automatically creates an index for the foreign key (because it does not exist and is not explicitly created), its name is index_name.

Requirements and Limitations

Foreign keys have the following requirements:

• Referenced columns in the parent table must be a an index or a prefix of an index.

• Foreign key columns and referenced columns must be of the same type, or similar types. For integer types, the size and sign must also be the same.

• For string types, the foreign key column and the referenced column must use the same character set and the same collation. Two columns that differ only in collation, such as utf8mb4_general_ci and utf8mb4_uca1400_ai_ci, cannot be compared, so a foreign key between them is rejected. See Changing the Character Set or Collation of a Foreign Key Column.

• Both foreign key columns and referenced columns can be PERSISTENT columns. However, the ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL clauses are not allowed in this case.

• The parent and the child table must use the same storage engine, and must not be TEMPORARY or partitioned tables. However, they can be the same table.

Foreign keys in MariaDB have the following limitations:

Current

Foreign key names must be unique per table.

< 12.1

Foreign key names must be unique per database.

• Supported only by InnoDB.

• Cannot be used with views.

• The SET DEFAULT action is not supported.

• Foreign key actions do not activate triggers.

• If ON UPDATE CASCADE recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT.

• Indexed generated columns (both VIRTUAL and PERSISTENT) are not supported as InnoDB foreign key indexes.

• The columns in the child table must be a BTREE index (not HASH, RTREE, or FULLTEXT — see SHOW INDEX), or the leftmost part of a BTREE index.

• Index prefixes are not supported, which means that TEXT and BLOB columns cannot be used as foreign keys.

Constraints

If a foreign keys exists, each row in the child table must match a row in the parent table. Multiple child rows can match the same parent row. A child row matches a parent row if all its foreign key values are identical to the row values in the parent table. However, if at least one of the foreign key values is NULL, the row has no parent, but it is still allowed.

MariaDB performs certain checks to guarantee that 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 DELETE clause 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 UPDATE clause of the foreign key.

• Trying to drop a table that is referenced by a foreign key produces a 1217 error (SQLSTATE '23000').

• A TRUNCATE TABLE statement against a table containing one or more foreign keys is executed as a DELETE without a WHERE clause, so that the foreign keys are enforced for each row.

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 ON DELETE and ON UPDATE.

• NO ACTION: Synonym for RESTRICT.

• 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 changes, too.

• SET NULL: The change is allowed, and the child row's foreign key columns are set to NULL.

• SET DEFAULT: This clause is not supported.

DELETE or UPDATE statements triggered by foreign keys do not activate triggers and are not counted in the Com_delete and Com_update status variables.

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.

Setting foreign_key_checks to 0 suspends the checks on data, but it does not permit schema changes that would leave the constraint itself invalid. Regardless of the setting, a column used in a foreign key cannot be dropped, renamed, or changed in a way that alters how its values compare, including a change of type, character set, or collation. See Changing the Character Set or Collation of a Foreign Key Column.

For detailed information about constraints, see this page.

Changing the Character Set or Collation of a Foreign Key Column

Because a foreign key requires both string columns to share a character set and collation, converting one table without the other breaks the constraint. MariaDB refuses the conversion rather than allowing it:

• Converting the child table fails with error 1832, Cannot change column '...': used in a foreign key constraint '...'.

• Converting the parent table fails with error 1833, Cannot change column '...': used in a foreign key constraint '...' of table '...'.

Setting foreign_key_checks to 0 does not lift this restriction, and neither does requesting ALGORITHM=COPY or ALGORITHM=INPLACE. The conversion is rejected in every combination of the two. This is deliberate: allowing it would leave a foreign key whose columns can no longer be compared, producing a schema that cannot be restored from a dump.

Instead, drop the constraint, convert both tables, and re-create the constraint. Dropping it in the same ALTER TABLE statement as the column change is supported, so the child table needs only one statement:

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE child
  DROP FOREIGN KEY fk_name,
  MODIFY col VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE parent
  MODIFY col VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE child
  ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent (col);

SET FOREIGN_KEY_CHECKS=1;

The order matters. The parent table cannot be converted while a child foreign key still references it, and a child's constraint cannot be dropped from within the parent's ALTER TABLE. Across a whole schema, drop every affected constraint first, convert all the tables, then re-create the constraints.

Re-creating a constraint while foreign_key_checks is 0 does not validate the existing rows against it. To have them validated, restore foreign_key_checks to 1 before the ADD CONSTRAINT statement.

To list the constraints that need dropping and re-creating, query the KEY_COLUMN_USAGE table:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE TABLE_SCHEMA = DATABASE()
   AND REFERENCED_TABLE_NAME IS NOT NULL;

Converting a table to a different collation changes how its values compare and sort, which also changes what counts as a duplicate in a unique index. Where a conversion is only needed to make two tables match, converting the older tables forward to the newer collation is usually preferable to moving the newer ones back. See Setting Character Sets and Collations.

Metadata

The REFERENTIAL_CONSTRAINTS Information Schema table contains information about foreign keys:

MariaDB [information_schema]> SELECT * FROM REFERENTIAL_CONSTRAINTS \G
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: nation
          CONSTRAINT_NAME: countries_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: nation
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: RESTRICT
              DELETE_RULE: RESTRICT
               TABLE_NAME: countries
    REFERENCED_TABLE_NAME: regions
*************************** 2. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: nation
          CONSTRAINT_NAME: regions_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: nation
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: RESTRICT
              DELETE_RULE: RESTRICT
               TABLE_NAME: regions
    REFERENCED_TABLE_NAME: continents
...

The individual columns are listed in the KEY_COLUMN_USAGE table:

MariaDB [information_schema]> SELECT * FROM KEY_COLUMN_USAGE LIMIT 2 \G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: nation
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: nation
                   TABLE_NAME: countries
                  COLUMN_NAME: country_id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 2. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: nation
              CONSTRAINT_NAME: country_code2
                TABLE_CATALOG: def
                 TABLE_SCHEMA: nation
                   TABLE_NAME: countries
                  COLUMN_NAME: country_code2
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL

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 table. Data about the individual columns are stored in INNODB_SYS_FOREIGN_COLS.

Another way of retrieving information about a table's foreign keys is the SHOW CREATE TABLE statement:

MariaDB [nation]> SHOW CREATE TABLE countries \G
*************************** 1. row ***************************
       Table: countries
Create Table: CREATE TABLE `countries` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `area` decimal(10,2) NOT NULL,
  `national_day` date DEFAULT NULL,
  `country_code2` char(2) NOT NULL,
  `country_code3` char(3) NOT NULL,
  `region_id` int(11) NOT NULL,
  PRIMARY KEY (`country_id`),
  UNIQUE KEY `country_code2` (`country_code2`),
  UNIQUE KEY `country_code3` (`country_code3`),
  KEY `region_id` (`region_id`),
  CONSTRAINT `countries_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `regions` (`region_id`)
) ENGINE=InnoDB AUTO_INCREMENT=240 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci

Examples

Creating and Using Foreign Keys

In this example, we create an author and a book table, both having a primary key called id. The book table also has a foreign key composed from a field called author_id, which refers to the author table primary key. The foreign key constraint name is optional, but we 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;

When trying to insert a book with a non-existing author, we 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 basically says that author_id is mandatory because otherwise the foreign key constraint is violated. So, let's 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());

Those INSERT statements first add an author name to the name column of the author table, and the id column is automatically updated thanks to AUTO_INCREMENT. For the next INSERT statement (adding the book or books), we use the LAST_INSERT_ID() function, which uses that newly generated ID, filling it in for the book table.

Now, let's delete the second author from the author table. When we created the foreign key, we specified ON DELETE CASCADE. This propagates the deletion to the book table, making the deleted author's books disappear, too:

DELETE FROM author WHERE name = 'H.P. Lovecraft';

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

We also specified ON UPDATE RESTRICT. This prevents 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)

Referencing Keys in Tables

This example demonstrates which keys to reference for foreign keys, and what happens when the wrong key (not_key) is referenced (error 1005):

CREATE TABLE a(a_key INT PRIMARY KEY, not_key INT);

CREATE TABLE b(for_key INT REFERENCES a(not_key));
ERROR 1005 (HY000): Can't create table `test`.`b` 
  (errno: 150 "Foreign key constraint is incorrectly formed")

CREATE TABLE c(for_key INT REFERENCES a(a_key));

SHOW CREATE TABLE c;
+-------+----------------------------------------------------------------------------------+
| Table | Create Table                                                                     |
+-------+----------------------------------------------------------------------------------+
| c     | CREATE TABLE `c` (
  `for_key` INT(11) DEFAULT NULL,
  KEY `for_key` (`for_key`),
  CONSTRAINT `c_ibfk_1` FOREIGN KEY (`for_key`) REFERENCES `a` (`a_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------+

INSERT INTO a VALUES (1,10);
Query OK, 1 row affected (0.004 sec)

INSERT INTO c VALUES (10);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
  (`test`.`c`, CONSTRAINT `c_ibfk_1` FOREIGN KEY (`for_key`) REFERENCES `a` (`a_key`))

INSERT INTO c VALUES (1);
Query OK, 1 row affected (0.004 sec)

SELECT * FROM c;
+---------+
| for_key |
+---------+
|       1 |
+---------+

_{This page is licensed: CC BY-SA / Gnu FDL}