(mysql.info) innodb-foreign-key-constraints
Info Catalog
(mysql.info) innodb-auto-increment-column
(mysql.info) using-innodb-tables
(mysql.info) innodb-and-mysql-replication
14.2.6.4 `FOREIGN KEY' Constraints
..................................
`InnoDB' also supports foreign key constraints. The syntax for a
foreign key constraint definition in `InnoDB' looks like this:
[CONSTRAINT SYMBOL] FOREIGN KEY [ID] (INDEX_COL_NAME, ...)
REFERENCES TBL_NAME (INDEX_COL_NAME, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
Foreign keys definitions are subject to the following conditions:
* Both tables must be `InnoDB' tables and they must not be
`TEMPORARY' tables.
* 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.
* 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' and `TEXT' columns cannot be
included in a foreign key, because indexes on those columns must
always include a prefix length.
* If the `CONSTRAINT SYMBOL' clause is given, the 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. The action `InnoDB' takes for
any `UPDATE' or `DELETE' operation that attempts to update or delete a
candidate key value in the parent table that has some matching rows in
the child table is dependent on the _referential action_ specified
using `ON UPDATE' and `ON DELETE' subclauses of the `FOREIGN KEY'
clause. When the user attempts to delete or update a row from a parent
table, and there are one or more matching rows in the child table,
`InnoDB' supports five options regarding the action to be taken:
* `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 CASCADE' and `ON UPDATE CASCADE' are
supported. Between two tables, you should not define several `ON
UPDATE CASCADE' clauses that act on the same column in the parent
table or in the child table.
* `SET NULL': Delete or update the row from the parent table and set
the foreign key column or columns in the child table to `NULL'.
This is valid only if the foreign key columns do not have the `NOT
NULL' qualifier specified. Both `ON DELETE SET NULL' and `ON
UPDATE SET NULL' clauses are supported.
* `NO ACTION': In standard SQL, `NO ACTION' means _no action_ in the
sense that an attempt to delete or update a primary key value is
not allowed to proceed if there is a related foreign key value in
the referenced table. `InnoDB' rejects the delete or update
operation for the parent table.
* `RESTRICT': Rejects the delete or update operation for the parent
table. `NO ACTION' and `RESTRICT' are the same as omitting the `ON
DELETE' or `ON UPDATE' clause. (Some database systems have
deferred checks, and `NO ACTION' is a deferred check. In MySQL,
foreign key constraints are checked immediately, so `NO ACTION'
and `RESTRICT' are the same.)
* `SET DEFAULT': This action is recognized by the parser, but
`InnoDB' rejects table definitions containing `ON DELETE SET
DEFAULT' or `ON UPDATE SET DEFAULT' clauses.
Note that `InnoDB' supports foreign key references within a table. In
these cases, `child table records' really refers to dependent records
within the same table.
`InnoDB' requires indexes on foreign keys and referenced keys so that
foreign key checks can be fast and not require a table scan. The index
on the foreign key is created automatically. 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.
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. If
you specify a `SET NULL' action, _make sure that you have not declared
the columns in the child table as `NOT NULL'_.
If MySQL reports an error number 1005 from a `CREATE TABLE' statement,
and the error message refers to errno 150, table creation failed
because a foreign key constraint was not correctly formed. Similarly,
if an `ALTER TABLE' fails and it refers to errno 150, that means a
foreign key definition would be incorrectly formed for the altered
table. You can use `SHOW ENGINE INNODB STATUS' to display a detailed
explanation of the most recent `InnoDB' foreign key error in the server.
* `InnoDB' does not check foreign key constraints on those
foreign key or referenced key values that contain a `NULL' column.
* Currently, triggers are not activated by cascaded foreign key
actions.
*Deviation from SQL standards*: If there are several rows in the parent
table that have the same referenced key value, `InnoDB' acts in foreign
key checks as if the other parent rows with the same key value do not
exist. For example, if you have defined a `RESTRICT' type constraint,
and there is a child row with several parent rows, `InnoDB' does not
allow the deletion of any of those parent rows.
`InnoDB' performs cascading operations through a depth-first algorithm,
based on records in the indexes corresponding to the foreign key
constraints.
*Deviation from SQL standards*: A `FOREIGN KEY' constraint that
references a non-`UNIQUE' key is not standard SQL. It is an `InnoDB'
extension to standard SQL.
*Deviation from SQL standards*: If `ON UPDATE CASCADE' or `ON UPDATE
SET NULL' recurses to update the _same table_ it has previously updated
during the cascade, it acts like `RESTRICT'. This means that you cannot
use self-referential `ON UPDATE CASCADE' or `ON UPDATE SET NULL'
operations. This is to prevent infinite loops resulting from cascaded
updates. A self-referential `ON DELETE SET NULL', on the other hand, is
possible, as is a self-referential `ON DELETE CASCADE'. Cascading
operations may not be nested more than 15 levels deep.
*Deviation from SQL standards*: Like MySQL in general, in an SQL
statement that inserts, deletes, or updates many rows, `InnoDB' checks
`UNIQUE' and `FOREIGN KEY' constraints row-by-row. According to the SQL
standard, the default behavior should be deferred checking. That is,
constraints are only checked after the _entire SQL statement_ has been
processed. Until `InnoDB' implements deferred constraint checking, some
things will be impossible, such as deleting a record that refers to
itself via a foreign key.
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' allows you to add a new foreign key constraint to a table by
using `ALTER TABLE':
ALTER TABLE TBL_NAME
ADD [CONSTRAINT SYMBOL] FOREIGN KEY [ID] (INDEX_COL_NAME, ...)
REFERENCES TBL_NAME (INDEX_COL_NAME, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
*Remember to create the required indexes first*. You can also add a
self-referential foreign key constraint to a table using `ALTER TABLE'.
`InnoDB' also supports the use of `ALTER TABLE' to drop foreign keys:
ALTER TABLE TBL_NAME DROP FOREIGN KEY FK_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.
The `InnoDB' parser allows table and column identifiers in a `FOREIGN
KEY ... REFERENCES ...' clause to be quoted within backticks.
(Alternatively, double quotes 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 to the dump
file, and does not forget about the foreign keys.
You can also display the foreign key constraints for a table like this:
SHOW TABLE STATUS FROM DB_NAME LIKE 'TBL_NAME';
The foreign key constraints are listed in the `Comment' column of the
output.
When performing foreign key checks, `InnoDB' sets shared row-level
locks on child or parent records it has to look at. `InnoDB' checks
foreign key constraints immediately; the check is not deferred to
transaction commit.
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 allows 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.
`InnoDB' does not allow 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 errno 150 in the error message.
Info Catalog
(mysql.info) innodb-auto-increment-column
(mysql.info) using-innodb-tables
(mysql.info) innodb-and-mysql-replication
automatically generated byinfo2html