InnoDB
supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB
looks like this:
[CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
, ...) REFERENCEStbl_name
(index_col_name
,...) [ON DELETEreference_option
] [ON UPDATEreference_option
]reference_option
: RESTRICT | CASCADE | SET NULL | NO ACTION
index_name
represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if InnoDB
creates an index for the foreign key, it uses index_name
for the index name.
Foreign keys definitions are subject to the following conditions:
-
Both tables must be
InnoDB
tables and they must not beTEMPORARY
tables. -
Corresponding columns in the foreign key and the referenced key must have similar internal data types inside
InnoDB
so that they can be compared without a type conversion. The size and sign of integer types 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. -
InnoDB
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 is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.)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 listed as the first columns in the same order. -
Index prefixes on foreign key columns are not supported. One consequence of this is that
BLOB
andTEXT
columns cannot be included in a foreign key because indexes on those columns must always include a prefix length. -
If the
CONSTRAINT
clause is given, thesymbol
symbol
value must be unique in the database. If the clause is not given,InnoDB
creates the name automatically.
InnoDB
rejects any INSERT
or UPDATE
operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table. When an UPDATE
or DELETE
operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential actionspecified using ON UPDATE
and ON DELETE
subclauses of the FOREIGN KEY
clause. InnoDB
supports five options regarding the action to be taken. If ON DELETE
or ON UPDATE
are not specified, the default action is RESTRICT
.
-
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.NoteCurrently, 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 a
SET 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. In MySQL, equivalent toRESTRICT
.InnoDB
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, andNO ACTION
is a deferred check. In MySQL, foreign key constraints are checked immediately, soNO ACTION
is the same asRESTRICT
. -
SET DEFAULT
: This action is recognized by the parser, butInnoDB
rejects table definitions containingON DELETE SET DEFAULT
orON UPDATE SET DEFAULT
clauses.
InnoDB
supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.
Examples of Foreign Key Clauses
Here is a simple example that relates parent
and child
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;
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 customer
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), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=INNODB;
InnoDB
enables you to add a new foreign key constraint to a table by using ALTER TABLE
:
ALTER TABLEtbl_name
ADD [CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
, ...) REFERENCEStbl_name
(index_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 using ALTER TABLE
, remember to create the required indexes first.
Foreign Keys and ALTER TABLE
InnoDB
supports the use of ALTER TABLE
to drop foreign keys:
ALTER TABLEtbl_name
DROP FOREIGN KEYfk_symbol
;
If the FOREIGN KEY
clause included a CONSTRAINT
name when you created the foreign key, you can refer to that name to drop the foreign key. Otherwise, the fk_symbol
value is internally generated by InnoDB
when the foreign key is created. To find out the symbol value when you want to drop a foreign key, use the SHOW CREATE TABLE
statement. For example:
mysql>SHOW CREATE TABLE ibtest11c\G
*************************** 1. row *************************** Table: ibtest11c Create Table: CREATE TABLE `ibtest11c` ( `A` int(11) NOT NULL auto_increment, `D` int(11) NOT NULL default '0', `B` varchar(200) NOT NULL default '', `C` varchar(175) default NULL, PRIMARY KEY (`A`,`D`,`B`), KEY `B` (`B`,`C`), KEY `C` (`C`), CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11a` (`A`, `D`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`) REFERENCES `ibtest11a` (`B`, `C`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=INNODB CHARSET=latin1 1 row in set (0.01 sec) mysql>ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;
You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE
statement. Separate statements are required.
If ALTER TABLE
for an InnoDB
table results in changes to column values (for example, because a column is truncated), InnoDB
's FOREIGN KEY
constraint checks do not notice possible violations caused by changing the values.
How Foreign Keys Work with Other MySQL Command
The InnoDB
parser permits table and column identifiers in a FOREIGN KEY ... REFERENCES ...
clause to be quoted within backticks. (Alternatively, double quotation marks can be used if the ANSI_QUOTES
SQL mode is enabled.) The InnoDB
parser also takes into account the setting of the lower_case_table_names
system variable.
InnoDB
returns a table's foreign key definitions as part of the output of the SHOW CREATE TABLE
statement:
SHOW CREATE TABLE tbl_name
;
mysqldump also produces correct definitions of tables in the dump file, and does not forget about the foreign keys.
To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set foreign_key_checks
to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. It is also possible to set this variable manually:
mysql>SET foreign_key_checks = 0;
mysql>SOURCE
mysql>dump_file_name
;SET foreign_key_checks = 1;
This enables you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys. It also speeds up the import operation. Setting foreign_key_checks
to 0 can also be useful for ignoring foreign key constraints during LOAD DATA
and ALTER TABLE
operations. However, even ifforeign_key_checks = 0
, InnoDB does not permit the creation of a foreign key constraint where a column references a nonmatching column type. Also, if an InnoDB
table has foreign key constraints, ALTER TABLE
cannot be used to change the table to use another storage engine. To alter the storage engine, drop any foreign key constraints first.
InnoDB
does not permit you to drop a table that is referenced by a FOREIGN KEY
constraint, unless you do SET foreign_key_checks = 0
. When you drop a table, the constraints that were defined in its create statement are also dropped.
If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.
If MySQL reports an error number 1005 from a