MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.5 FOREIGN KEY Constraints (original) (raw)

13.1.18.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.6.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 RESTRICT.

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

As of NDB 7.5.14 and NDB 7.6.10: ForNDB 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.

In MySQL 5.7.13 and earlier, InnoDB does not permit defining a foreign key constraint with a cascading referential action on thebase column of an indexed virtual generated column. This restriction is lifted in MySQL 5.7.14.

In MySQL 5.7.13 and earlier, InnoDB does not permit defining cascading referential actions on non-virtual foreign key columns that are explicitly included in a virtual index. This restriction is lifted in MySQL 5.7.14.

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(11) DEFAULT NULL,
  `parent_id` int(11) 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=latin1

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 5.1.8, “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:

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(11) DEFAULT NULL,
  `parent_id` int(11) 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=latin1

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_SYS_FOREIGN andINNODB_SYS_FOREIGN_COLS tables. Example queries are show here:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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_SYS_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
------------------------
2014-10-16 18:35:18 0x7fc2a95c1700 Transaction:
TRANSACTION 1814, 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 2, OS thread handle 140474041767680, query id 74 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 `mysql`.`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 `mysql`.`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 00000000070a; asc       ;;
 2: len 7; hex aa0000011d0134; asc       4;;
...

Warning

ER_NO_REFERENCED_ROW_2 andER_ROW_IS_REFERENCED_2 error messages for foreign key operations expose information about parent tables, even if the user has no parent table access privileges. To hide information about parent tables, include the appropriate condition handlers in application code and stored programs.