Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.4Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

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 aCREATE TABLE orALTER TABLE statement includes the following:

[CONSTRAINT [symbol]] FOREIGN KEY    [index_name] (col_name, ...)    REFERENCEStbl_name (col_name,...)    [ON DELETEreference_option]    [ON UPDATEreference_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:

  • TheCONSTRAINTsymbol value is used, if defined.

  • If theCONSTRAINTsymbol clause is not defined, or a symbol is not included following theCONSTRAINT keyword:

    • ForInnoDB tables, a constraint name is generated automatically.

    • ForNDB tables, theFOREIGN KEYindex_name value is used, if defined. Otherwise, a constraint name is generated automatically.

  • TheCONSTRAINTsymbol value, if defined, must be unique in the database. A duplicatesymbol results in an error similar to:ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121).

Table and column identifiers in aFOREIGN 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:

  • Parent and child tables must use the same storage engine, and they cannot be defined as temporary tables.

  • Creating a foreign key constraint requires theREFERENCES privilege on the parent table.

  • Corresponding columns in the foreign key and the referenced key must have similar data types.The size and sign of fixed precision types such asINTEGER andDECIMAL must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

  • MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, a“child table record” refers to a dependent record within the same table.

  • MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as thefirst columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint.index_name, if given, is used as described previously.

  • InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are thefirst columns in the same order. Hidden columns thatInnoDB adds to an index are also considered (seeSection 14.6.2.1, “Clustered and Secondary Indexes”).

    NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key.InnoDB does not, which is an extension of standard SQL.

  • Index prefixes on foreign key columns are not supported. Consequently,BLOB andTEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.

  • InnoDB does not currently support foreign keys for tables with user-defined partitioning. This includes both parent and child tables.

    This restriction does not apply forNDB tables that are partitioned byKEY orLINEAR KEY (the only user partitioning types supported by theNDB storage engine); these may have foreign key references or be the targets of such references.

  • A table in a foreign key relationship cannot be altered to use another storage engine. To change the storage engine, you must drop any foreign key constraints first.

  • A foreign key constraint cannot reference a virtual generated column.

  • Prior to 5.7.16, a foreign key constraint cannot reference a secondary index defined on a virtual generated column.

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 anUPDATE orDELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on thereferential action specified byON UPDATE andON DELETE subclauses of theFOREIGN KEY clause. Referential actions include:

  • CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. BothON DELETE CASCADE andON UPDATE CASCADE are supported. Between two tables, do not define severalON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

    If aFOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables a parent and child, anON UPDATE CASCADE orON DELETE CASCADE subclause defined for oneFOREIGN KEY clause must be defined for the other in order for cascading operations to succeed. If anON UPDATE CASCADE orON DELETE CASCADE subclause is only defined for oneFOREIGN KEY clause, cascading operations fail with an error.

    Note

    Cascaded foreign key actions do not activate triggers.

  • SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table toNULL. BothON DELETE SET NULL andON UPDATE SET NULL clauses are supported.

    If you specify aSET NULL action,make sure that you have not declared the columns in the child table asNOT NULL.

  • RESTRICT: Rejects the delete or update operation for the parent table. SpecifyingRESTRICT (orNO ACTION) is the same as omitting theON DELETE orON UPDATE clause.

  • NO ACTION: A keyword from standard SQL. ForInnoDB, this is equivalent toRESTRICT; the delete or update operation for the parent table is immediately rejected if there is a related foreign key value in the referenced table.NDB supports deferred checks, andNO ACTION specifies a deferred check; when this is used, constraint checks are not performed until commit time. Note that forNDB tables, this causes all foreign key checks made for both parent and child tables to be deferred.

  • SET DEFAULT: This action is recognized by the MySQL parser, but bothInnoDB andNDB reject table definitions containingON DELETE SET DEFAULT orON UPDATE SET DEFAULT clauses.

For storage engines that support foreign keys, MySQL rejects anyINSERT 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 anON DELETE orON UPDATE that is not specified, the default action is alwaysRESTRICT.

ForNDB 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 useCASCADE,SET NULL, orSET DEFAULT asON UPDATE referential actions, nor can it useSET NULL orSET DEFAULT asON DELETE referential actions.

A foreign key constraint on the base column of a stored generated column cannot useCASCADE,SET NULL, orSET DEFAULT asON UPDATE orON 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 avirtual index. This restriction is lifted in MySQL 5.7.14.

Foreign Key Constraint Examples

This simple example relatesparent 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 theproduct 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 followingALTER TABLE syntax:

ALTER TABLEtbl_name    ADD [CONSTRAINT [symbol]] FOREIGN KEY    [index_name] (col_name, ...)    REFERENCEStbl_name (col_name,...)    [ON DELETEreference_option]    [ON UPDATEreference_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 TABLEtbl_name DROP FOREIGN KEYfk_symbol;

If theFOREIGN 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, useSHOW CREATE TABLE:

mysql> SHOW CREATE TABLE child\G*************************** 1. row ***************************       Table: childCreate 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=latin1mysql> 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 onNDB tables as it does forInnoDB tables.

Theforeign_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:

  • Dropping a table that is referenced by a foreign key constraint. A referenced table can only be dropped afterforeign_key_checks is disabled. When you drop a table, constraints defined on the table are also dropped.

  • Reloading tables in different order than required by their foreign key relationships. For example,mysqldump produces correct definitions of tables in the dump file, including foreign key constraints for child tables. To make it easier to reload dump files for tables with foreign key relationships,mysqldump automatically includes a statement in the dump output that disablesforeign_key_checks. This enables you to import the tables in any order in case the dump file contains tables that are not correctly ordered for foreign keys. Disablingforeign_key_checks also speeds up the import operation by avoiding foreign key checks.

  • ExecutingLOAD DATA operations, to avoid foreign key checking.

  • Performing anALTER TABLE operation on a table that has a foreign key relationship.

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

  • Recreating a table that was previously dropped returns an error if the table definition does not conform to the foreign key constraints that reference the table. The table must have the correct column names and types. It must also have indexes on the referenced keys. If these requirements are not satisfied, MySQL returns Error 1005 that refers to errno: 150 in the error message, which means that a foreign key constraint was not correctly formed.

  • Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table.

  • Dropping an index required by a foreign key constraint. The foreign key constraint must be removed before dropping the index.

  • Creating a foreign key constraint where a column references a nonmatching column type.

Disablingforeign_key_checks has these additional implications:

  • It is permitted to drop a database that contains tables with foreign keys that are referenced by tables outside the database.

  • It is permitted to drop a table with foreign keys referenced by other tables.

  • Enablingforeign_key_checks does not trigger a scan of table data, which means that rows added to a table whileforeign_key_checks is disabled are not checked for consistency whenforeign_key_checks is re-enabled.

Foreign Key Definitions and Metadata

To view a foreign key definition, useSHOW CREATE TABLE:

mysql> SHOW CREATE TABLE child\G*************************** 1. row ***************************       Table: childCreate 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_1FOR_NAME: test/childREF_NAME: test/parent  N_COLS: 1    TYPE: 1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS \G*************************** 1. row ***************************          ID: test/child_ibfk_1FOR_COL_NAME: parent_idREF_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 insertingmysql tables in use 1, locked 14 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3MySQL thread id 2, OS thread handle 140474041767680, query id 74 localhostroot updateINSERT 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 CASCADETrying 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.