Redes

MySQL :: MySQL 5.5 Reference Manual :: 14.3.5.4 FOREIGN KEY Co... http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Skip navigation links The world's most popular open source database

Login | Register

Developer Zone Downloads Documentation MySQL Server MySQL Enterprise MySQL Workbench MySQL Cluster Topic Guides Expert Guides Other Docs Archives About

MySQL 5.5Reference Manual :: 14 Storage Engines :: 14.3 The InnoDB Storage Engine :: 14.3.5 Creating and Using InnoDB Tables :: 14.3.5.4 FOREIGN KEY Constraints

14.3.5.4. FOREIGN KEY Constraints
InnoDB supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. The syntax for a foreign key constraintdefinition 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 :

« 14.3.5.3 AUTO_INCREMENT Handling in InnoDB 14.3.5.5 InnoDB and MySQL Replication »

Section Navigation

[Toggle]

14.3.5 Creating and Using InnoDB Tables14.3.5.1 Using InnoDB Transactions 14.3.5.2 Converting Tables from Other Storage Engines to InnoDB 14.3.5.3 AUTO_INCREMENT Handling in InnoDB 14.3.5.4 FOREIGN KEY Constraints 14.3.5.5 InnoDB and MySQL Replication

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, ifInnoDB creates an index for the foreign key, it uses index_name for the index name.

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 KEY clause is specified in the child table. The parent and child tables must both beInnoDB tables. 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 collationmust 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 index might be silently droppedlater, if you create another index that can be used to enforce the foreign key constraint. 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 keycolumns 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 attemptsto 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 action specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. InnoDB supports five options regarding the...