MySQL :: MySQL 8.4 Reference Manual :: 15.1.20.5 FOREIGN KEY Constraints (original) (raw)

15.1.20.5 FOREIGN KEY Constraints

MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent.

A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.

The essential syntax for a defining a foreign key constraint in a CREATE TABLE orALTER TABLE statement includes the following:

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

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

Foreign key constraint usage is described under the following topics in this section:

Identifiers

Foreign key constraint naming is governed by the following rules:

Table and column identifiers in a FOREIGN KEY ... REFERENCES clause can be quoted within backticks (`). Alternatively, double quotation marks (") can be used if theANSI_QUOTES SQL mode is enabled. Thelower_case_table_names system variable setting is also taken into account.

Conditions and Restrictions

Foreign key constraints are subject to the following conditions and restrictions:

For information about how the MySQL implementation of foreign key constraints differs from the SQL standard, seeSection 1.7.2.3, “FOREIGN KEY Constraint Differences”.

Referential Actions

When an UPDATE orDELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by ON UPDATE and ON DELETE subclauses of theFOREIGN KEY clause. Referential actions include:

For storage engines that support foreign keys, MySQL rejects any INSERT orUPDATE operation that attempts to create a foreign key value in a child table if there is no matching candidate key value in the parent table.

For an ON DELETE or ON UPDATE that is not specified, the default action is always NO ACTION.

As the default, an ON DELETE NO ACTION orON UPDATE NO ACTION clause that is specified explicitly does not appear inSHOW CREATE TABLE output or in tables dumped with mysqldump.RESTRICT, which is an equivalent non-default keyword, appears in SHOW CREATE TABLE output and in tables dumped withmysqldump.

For NDB tables, ON UPDATE CASCADE is not supported where the reference is to the parent table's primary key.

For NDB tables, ON DELETE CASCADE is not supported where the child table contains one or more columns of any of theTEXT orBLOB types. (Bug #89511, Bug #27484882)

InnoDB performs cascading operations using a depth-first search algorithm on the records of the index that corresponds to the foreign key constraint.

A foreign key constraint on a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE referential actions, nor can it useSET NULL or SET DEFAULT as ON DELETE referential actions.

A foreign key constraint on the base column of a stored generated column cannot use CASCADE,SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.

Foreign Key Constraint Examples

This simple example relates parent andchild tables through a single-column foreign key:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

This is a more complex example in which aproduct_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in thecustomer table:

CREATE TABLE product (
    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=INNODB;

CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)   ENGINE=INNODB;
Adding Foreign Key Constraints

You can add a foreign key constraint to an existing table using the following ALTER TABLE syntax:

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table usingALTER TABLE, remember to first create an index on the column(s) referenced by the foreign key.

Dropping Foreign Key Constraints

You can drop a foreign key constraint using the followingALTER TABLE syntax:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

If the FOREIGN KEY clause defined aCONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint. Otherwise, a constraint name was generated internally, and you must use that value. To determine the foreign key constraint name, use SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
  REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;

Adding and dropping a foreign key in the sameALTER TABLE statement is supported forALTER TABLE ... ALGORITHM=INPLACE. It is not supported forALTER TABLE ... ALGORITHM=COPY.

Foreign Key Checks

In MySQL, InnoDB and NDB tables support checking of foreign key constraints. Foreign key checking is controlled by theforeign_key_checks variable, which is enabled by default. Typically, you leave this variable enabled during normal operation to enforce referential integrity. Theforeign_key_checks variable has the same effect on NDB tables as it does for InnoDB tables.

The foreign_key_checks variable is dynamic and supports both global and session scopes. For information about using system variables, seeSection 7.1.9, “Using System Variables”.

Disabling foreign key checking is useful when:

When foreign_key_checks is disabled, foreign key constraints are ignored, with the following exceptions:

Disabling foreign_key_checks has these additional implications:

Locking

MySQL extends metadata locks, as necessary, to tables that are related by a foreign key constraint. Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables. This feature also enables updates to foreign key metadata when a parent table is modified. In earlier MySQL releases, foreign key metadata, which is owned by the child table, could not be updated safely.

If a table is locked explicitly with LOCK TABLES, any tables related by a foreign key constraint are opened and locked implicitly. For foreign key checks, a shared read-only lock (LOCK TABLES READ) is taken on related tables. For cascading updates, a shared-nothing write lock (LOCK TABLES WRITE) is taken on related tables that are involved in the operation.

Foreign Key Definitions and Metadata

To view a foreign key definition, useSHOW CREATE TABLE:

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
  REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

You can obtain information about foreign keys from the Information SchemaKEY_COLUMN_USAGE table. An example of a query against this table is shown here:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
       WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |
+--------------+------------+-------------+-----------------+
| test         | child      | parent_id   | child_ibfk_1    |
+--------------+------------+-------------+-----------------+

You can obtain information specific toInnoDB foreign keys from theINNODB_FOREIGN andINNODB_FOREIGN_COLS tables. Example queries are show here:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G
*************************** 1. row ***************************
      ID: test/child_ibfk_1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS \G
*************************** 1. row ***************************
          ID: test/child_ibfk_1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0
Foreign Key Errors

In the event of a foreign key error involvingInnoDB tables (usually Error 150 in the MySQL Server), information about the latest foreign key error can be obtained by checkingSHOW ENGINE INNODB STATUS output.

mysql> SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-04-12 14:57:24 0x7f97a9c91700 Transaction:
TRANSACTION 7717, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 8, OS thread handle 140289365317376, query id 14 localhost root update
INSERT INTO child VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6)
Foreign key constraint fails for table `test`.`child`:
,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE
  CASCADE ON UPDATE CASCADE
Trying to add in child table, in index par_ind tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000003; asc     ;;

But in parent table `test`.`parent`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000001e19; asc       ;;
 2: len 7; hex 81000001110137; asc       7;;
...

Warning

If a user has table-level privileges for all parent tables,ER_NO_REFERENCED_ROW_2 andER_ROW_IS_REFERENCED_2 error messages for foreign key operations expose information about parent tables. If a user does not have table-level privileges for all parent tables, more generic error messages are displayed instead (ER_NO_REFERENCED_ROW andER_ROW_IS_REFERENCED).

An exception is that, for stored programs defined to execute with DEFINER privileges, the user against which privileges are assessed is the user in the programDEFINER clause, not the invoking user. If that user has table-level parent table privileges, parent table information is still displayed. In this case, it is the responsibility of the stored program creator to hide the information by including appropriate condition handlers.