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, …)
REFERENCES tbl_name (index_col_name,…)
[ON DELETE reference_option]
[ON UPDATE reference_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 be TEMPORARY 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 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. 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. 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.
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.
(No Ratings Yet)