MySQL supports foreign keys, which let you cross-reference
related data across tables, and
constraints, which help keep this spread-out data
consistent. The essential syntax for a foreign key constraint
definition in a
CREATE TABLE or
ALTER TABLE statement looks like
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
index_name represents a foreign key
index_name value is ignored
if there is already an explicitly defined index on the child
table that can support the foreign key. Otherwise, MySQL
implicitly creates a foreign key index that is named according
to the following rules:
If defined, the
symbolvalue is used. Otherwise, the
index_namevalue is used.
If neither a
index_nameis defined, the foreign key index name is generated using the name of the referencing foreign key column.
Foreign keys definitions are subject to the following conditions:
Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The
FOREIGN KEYclause is specified in the child table. The parent and child tables must use the same storage engine. They must not be
Corresponding columns in the foreign key and the referenced key must have similar data types. 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.
foreign_key_checksis enabled, which is the default setting, character set conversion is not permitted on tables that include a character string column used in a foreign key constraint. The workaround is described in Section 13.1.7, “ALTER TABLE Syntax”.
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 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
TEXTcolumns cannot be included in a foreign key because indexes on those columns must always include a prefix length.
CONSTRAINTclause is given, the
symbolvalue, if used, must be unique in the database. A duplicate
symbolwill result in an error similar to: ERROR 1005 (HY000): Can't create table 'test.#sql-211d_3' (errno: 121). If the clause is not given, or a
symbolis not included following the
CONSTRAINTkeyword, a name for the constraint is created automatically.
InnoDBdoes not currently support foreign keys for tables with user-defined partitioning. This includes both parent and child tables.
This section describes how foreign keys help guarantee referential integrity.
For storage engines supporting foreign keys, MySQL rejects any
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.
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 using
ON DELETE subclauses of the
FOREIGN KEY clause. MySQL supports five
options regarding the action to be taken, listed here:
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
SET DEFAULT: This action is recognized by the MySQL parser, but
InnoDBrejects table definitions containing
ON DELETE SET DEFAULTor
ON UPDATE SET DEFAULTclauses.
ON DELETE or
UPDATE that is not specified, the default action is
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, “child table records” really refers to dependent records within the same table.
Here is a simple example that 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;
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
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 new foreign key constraint to an existing table by
ALTER TABLE. The syntax
relating to foreign keys for this statement is shown here:
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_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 create the required indexes first.
You can also use
ALTER TABLE to
drop foreign keys, using the syntax shown here:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
FOREIGN KEY clause included a
CONSTRAINT name when you created the foreign
key, you can refer to that name to drop the foreign key.
fk_symbol value is
generated internally when the foreign key is created. To find
out the symbol value when you want to drop a foreign key, use a
SHOW CREATE TABLE statement, as
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`;
Adding and dropping a foreign key in separate clauses of a
ALTER TABLE statement may
be problematic in some cases and is therefore unsupported. Use
separate statements for each operation.
ALTER TABLE statement
results in changes to column values (for example, because a
column is truncated), MySQL's foreign key constraint checks
do not notice possible violations caused by changing the values.
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
enabled. The setting of the
variable is also taken into account.
You can view a child table's foreign key definitions as
part of the output of the
SHOW CREATE TABLE tbl_name;
You can also obtain information about foreign keys by querying
mysqldump produces correct definitions of tables in the dump file, including the foreign keys for child tables.
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 dump_file_name; mysql> 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,
foreign_key_checks = 0,
MySQL does not permit the creation of a foreign key constraint
where a column references a nonmatching column type. Also, if a
table has foreign key constraints,
TABLE cannot be used to alter the table to use another
storage engine. To change the storage engine, you must drop any
foreign key constraints first.
You cannot issue
DROP TABLE for a
table that is referenced by a
constraint, unless you do
SET foreign_key_checks =
0. When you drop a table, any constraints that were
defined in the statement used to create that table are also
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 correct column names and types,
and it must have indexes on the referenced keys, as stated
earlier. If these are not satisfied, MySQL returns Error 1005
and refers to Error 150 in the error message, which means that a
foreign key constraint was not correctly formed. Similarly, if
ALTER TABLE fails due to Error
150, this means that a foreign key definition would be
incorrectly formed for the altered table.
InnoDB tables, you can obtain a detailed
explanation of the most recent
key error in the MySQL Server, by checking the output of
SHOW ENGINE INNODB
For users familiar with the ANSI/ISO SQL Standard, please note
that no storage engine, including
recognizes or enforces the
used in referential-integrity constraint definitions. Use of
MATCH clause will not have the
specified effect, and also causes
ON UPDATE clauses to be ignored. For
these reasons, specifying
MATCH should be
MATCH clause in the SQL standard
NULL values in a composite
(multiple-column) foreign key are handled when comparing to a
primary key. MySQL essentially implements the semantics
MATCH SIMPLE, which permit a
foreign key to be all or partially
that case, the (child table) row containing such a foreign key
is permitted to be inserted, and does not match any row in the
referenced (parent) table. It is possible to implement other
semantics using triggers.
Additionally, MySQL requires that the referenced columns be
indexed for performance reasons. However, the system does not
enforce a requirement that the referenced columns be
UNIQUE or be declared
NULL. The handling of foreign key references to
nonunique keys or keys that contain
values is not well defined for operations such as
CASCADE. You are advised to use foreign keys that
Furthermore, MySQL parses but ignores “inline
REFERENCES specifications” (as
defined in the SQL standard) where the references are defined
as part of the column specification. MySQL accepts
REFERENCES clauses only when specified as
part of a separate
specification. For storage engines that do not support foreign
keys (such as
Server parses and ignores foreign key specifications.