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.
[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:
Foreign key constraint naming is governed by the following rules:
symbolvalue is used, if defined.
symbolclause is not defined, or a symbol is not included following the
InnoDBtables, a constraint name is generated automatically.
index_namevalue is used, if defined. Otherwise, a constraint name is generated automatically.
CONSTRAINTvalue, if defined, must be unique in the database. A duplicate
symbolresults in an error similar to: ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121).
Table and column identifiers in a
FOREIGN KEY ...
REFERENCES clause can be quoted within backticks
`). Alternatively, double quotation marks
") can be used if the
ANSI_QUOTES SQL mode is
variable setting is also taken into account.
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.
Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of fixed precision types such as
DECIMALmust 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 the first 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.
InnoDBpermits 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 the first columns in the same order. Hidden columns that
InnoDBadds to an index are also considered (see Section 184.108.40.206, “Clustered and Secondary Indexes”).
NDBrequires an explicit unique key (or primary key) on any column referenced as a foreign key.
InnoDBdoes not, which is an extension of standard SQL.
InnoDBdoes not currently support foreign keys for tables with user-defined partitioning. This includes both parent and child tables.
This restriction does not apply for
NDBtables that are partitioned by
LINEAR KEY(the only user partitioning types supported by the
NDBstorage 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.
For information about how the MySQL implementation of foreign key constraints differs from the SQL standard, see Section 220.127.116.11, “FOREIGN KEY Constraint Differences”.
DELETE 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 DELETE subclauses of the
FOREIGN KEY clause. Referential actions
CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both
ON DELETE CASCADEand
ON UPDATE CASCADEare supported. Between two tables, do not define several
ON UPDATE CASCADEclauses that act on the same column in the parent table or in the child table.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 to
ON DELETE SET NULLand
ON UPDATE SET NULLclauses are supported.
If you specify a
SET NULLaction, make sure that you have not declared the columns in the child table as
RESTRICT: Rejects the delete or update operation for the parent table. Specifying
NO ACTION) is the same as omitting the
NO ACTION: A keyword from standard SQL. In MySQL, equivalent to
RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and
NO ACTIONis a deferred check. In MySQL, foreign key constraints are checked immediately, so
NO ACTIONis the same as
For storage engines that support foreign keys, MySQL rejects
UPDATE 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.
ON DELETE or
UPDATE that is not specified, the default action is
UPDATE CASCADE is not supported where the reference
is to the parent table's primary key.
InnoDB performs cascading operations using
a depth-first search algorithm on the records of the index
that corresponds to the foreign key constraint.
This simple example relates
child tables through a single-column
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 a
product_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 the
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;
You can add a foreign key constraint to an existing table
using the following
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 using
ALTER TABLE, remember
to first create an index on the column(s) referenced by the
You can drop a foreign key constraint using the following
ALTER TABLE syntax:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
FOREIGN KEY clause defined a
CONSTRAINT 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
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`;
Foreign key checking is controlled by the
which is enabled by default. Typically, you leave this
variable enabled during normal operation to enforce
referential integrity. Beginning with MySQL NDB Cluster 7.3.2,
variable has the same effect on
NDB tables as it does for
InnoDB tables. Previously, the
setting was ignored for NDB tables and all such checks were
enforced (Bug #14095855).
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 after
foreign_key_checksis 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 disables
foreign_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. Disabling
foreign_key_checksalso speeds up the import operation by avoiding foreign key checks.
LOAD DATAoperations, to avoid foreign key checking.
ALTER TABLEoperation on a table that has a foreign key relationship.
disabled, foreign key constraints are ignored, with the
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.
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.
foreign_key_checksdoes not trigger a scan of table data, which means that rows added to a table while
foreign_key_checksis disabled are not checked for consistency when
To view a foreign key definition, 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
You can obtain information about foreign keys from the
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 | +--------------+------------+-------------+-----------------+
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
In the event of a foreign key error involving
InnoDB tables (usually Error 150 in the
MySQL Server), information about the latest foreign key error
can be obtained by checking
INNODB STATUS output.
mysql> SHOW ENGINE INNODB STATUS\G ... ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2014-10-17 09:51:31 7f47bcde6700 Transaction: TRANSACTION 436786, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 3 MySQL thread id 1, OS thread handle 0x7f47bcde6700, query id 96 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 00000006aa26; asc &;; 2: len 7; hex 9d000001610137; asc a 7;; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2014-10-17 09:52:38 7f47bcde6700 *** (1) TRANSACTION: TRANSACTION 436801, ACTIVE 12 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 2, OS thread handle 0x7f47bcda5700, query id 102 localhost root updating DELETE FROM t WHERE i = 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 3693 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `mysql`.`t` trx id 436801 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000003a00; asc : ;; 1: len 6; hex 00000006aa3f; asc ?;; 2: len 7; hex ad0000021d0110; asc ;; 3: len 4; hex 80000001; asc ;; ...
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