# Foreign Keys

## 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](https://app.gitbook.com/s/WCInJQ9cmGjq1lsTG91E/database-theory/relational-databases-foreign-keys).

{% hint style="info" %}
Foreign keys can only be used with storage engines that support them. The default [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) supports foreign keys.
{% endhint %}

{% hint style="warning" %}
[Partitioned tables](https://mariadb.com/docs/server/server-usage/partitioning-tables) cannot contain foreign keys, and cannot be referenced by a foreign key.
{% endhint %}

## Syntax

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

MariaDB applies the constraint if possible. See the [Examples](#references) below.

Foreign keys are created with [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) or [ALTER TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table). The foreign key definition has this syntax:

```sql
[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
```

{% hint style="info" %}
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.
{% endhint %}

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.
* Both foreign key columns and referenced columns can be [PERSISTENT](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/generated-columns) 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:

{% tabs %}
{% tab title="Current" %}
Foreign key names must be **unique per table**.
{% endtab %}

{% tab title="< 12.1" %}
Foreign key names must be **unique per database**.
{% endtab %}
{% endtabs %}

* Supported only by InnoDB.
* Cannot be used with views.
* The `SET DEFAULT` action is not supported.
* Foreign key actions do not activate [triggers](https://mariadb.com/docs/server/server-usage/triggers-events/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](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/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](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-index)), or the leftmost part of a `BTREE` index.
* Index prefixes are not supported, which means that [TEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/text) and [BLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/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](https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-1400-to-1499/e1452) ([SQLSTATE](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-diagnostics/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](https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-1200-to-1299/e1217) ([SQLSTATE](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-diagnostics/sqlstate) '23000').
* A [TRUNCATE TABLE](https://mariadb.com/docs/server/reference/sql-statements/table-statements/truncate-table) statement against a table containing one or more foreign keys is executed as a [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/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](https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-1400-to-1499/e1451) ([SQLSTATE](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-diagnostics/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](https://mariadb.com/docs/server/server-usage/triggers-events/triggers) and are not counted in the [Com\_delete](https://mariadb.com/docs/server/server-management/variables-and-modes/server-status-variables#com_delete) and [Com\_update](https://mariadb.com/docs/server/server-management/variables-and-modes/server-status-variables#com_update) status variables.

Foreign key constraints can be disabled by setting the [foreign\_key\_checks](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#foreign_key_checks) server system variable to `0`. This speeds up the insertion of large quantities of data.

{% hint style="info" %}
For detailed information about constraints, see [this page](https://mariadb.com/docs/server/reference/sql-statements/data-definition/constraint).
{% endhint %}

## Metadata

The [REFERENTIAL\_CONSTRAINTS](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-referential_constraints-table) Information Schema table contains information about foreign keys:

{% code expandable="true" %}

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

{% endcode %}

The individual columns are listed in the [KEY\_COLUMN\_USAGE](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-key_column_usage-table) table:

{% code expandable="true" %}

```sql
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
```

{% endcode %}

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](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-innodb-tables/information-schema-innodb_sys_foreign-table) table. Data about the individual columns are stored in [INNODB\_SYS\_FOREIGN\_COLS](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-innodb-tables/information-schema-innodb_sys_foreign-table).

Another way of retrieving information about a table's foreign keys is the [SHOW CREATE TABLE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-table) statement:

{% code overflow="wrap" expandable="true" %}

```sql
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
```

{% endcode %}

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

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-1000-to-1099/e1005)):

{% code expandable="true" %}

```sql
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 |
+---------+
```

{% endcode %}

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/foreign-keys.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.