“No Comment….. from“
FOREIGN KEY Constraints
InnoDB supports foreign key constraints. The syntax for a foreign key constraint definition in
InnoDB looks like this:
symbol]] FOREIGN KEY [
index_col_name, ...) REFERENCES
index_col_name,...) [ON DELETE
reference_option] [ON UPDATE
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
InnoDBtables and they must not be
- Corresponding columns in the foreign key and the referenced key must have similar internal data types inside
InnoDBso 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.
InnoDBrequires 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.
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 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.
- If the
CONSTRAINTclause is given, the
symbolvalue must be unique in the database. If the clause is not given,
InnoDBcreates the name automatically.
InnoDB 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. The action
InnoDB takes for any
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. If
ON DELETE or
ON UPDATE are not specified, the default action is
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, you should not define several
ON UPDATE CASCADEclauses that act on the same column in the parent table or in the child table.Note
Currently, 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
NULL. This is valid only if the foreign key columns do not have the
NOT NULLqualifier specified. Both
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
NO ACTION: In standard SQL,
NO ACTIONmeans no action in the sense that an attempt to delete or update a primary key value is not permitted to proceed if there is a related foreign key value in the referenced table.
InnoDBrejects the delete or update operation for the parent table.
RESTRICT: Rejects the delete or update operation for the parent table. Specifying
NO ACTION) is the same as omitting the
ON UPDATEclause. (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 parser, but
InnoDBrejects table definitions containing
ON DELETE SET DEFAULTor
ON UPDATE SET DEFAULTclauses.
InnoDB supports foreign key references within a table. 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 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
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
tbl_nameADD [CONSTRAINT [
symbol]] FOREIGN KEY [
index_col_name, ...) REFERENCES
index_col_name,...) [ON DELETE
reference_option] [ON UPDATE
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.
InnoDB supports the use of
ALTER TABLE to drop foreign keys:
tbl_nameDROP FOREIGN KEY
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:
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.
ALTER TABLE for an
InnoDB table results in changes to column values (for example, because a column is truncated),
FOREIGN KEY constraint checks do not notice possible violations caused by changing the values.
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
mysqldump also produces correct definitions of tables in 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
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:
SET foreign_key_checks = 0;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, even if
foreign_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 TABLEcannot be used to change the table to use another storage engine. To alter the storage engine, you must 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
CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an
ALTER TABLE fails and it refers to error 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.
For users familiar with the ANSI/ISO SQL Standard, please note that no storage engine, including
InnoDB, recognizes or enforces the
MATCH clause used in referential-integrity constraint definitions. Use of an explicit
MATCH clause will not have the specified effect, and also causes
ON DELETE and
ON UPDATE clauses to be ignored. For these reasons, specifying
MATCH should be avoided.
MATCH clause in the SQL standard controls how
NULL values in a composite (multiple-column) foreign key are handled when comparing to a primary key.
InnoDB essentially implements the semantics defined by
MATCH SIMPLE, which permit a foreign key to be all or partially
NULL. In 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 and
InnoDB require that the referenced columns be indexed for performance. However, the system does not enforce a requirement that the referenced columns be
UNIQUE or be declared
NOT NULL. The handling of foreign key references to nonunique keys or keys that contain
NULL values is not well defined for operations such as
DELETE CASCADE. You are advised to use foreign keys that reference only
NOT NULL keys.
InnoDB does not recognize or support “inline
REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification.
REFERENCESclauses only when specified as part of a separate
FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications.
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 permit 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 thesame 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,
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 using a foreign key.