Movatterモバイル変換

Foreign keys let you cross-reference related data across tables, andforeign key constraints help keep this spread-out data consistent.

MySQL supportsON UPDATE andON DELETE foreign key references inCREATE TABLE andALTER TABLE statements. The available referential actions areRESTRICT (the default),CASCADE,SET NULL, andNO ACTION.

SET DEFAULT is also supported by the MySQL Server but is currently rejected as invalid byInnoDB. Since MySQL does not support deferred constraint checking,NO ACTION is treated asRESTRICT. For the exact syntax supported by MySQL for foreign keys, seeSection 13.1.18.5, “FOREIGN KEY Constraints”.

MATCH FULL,MATCH PARTIAL, andMATCH SIMPLE are allowed, but their use should be avoided, as they cause the MySQL Server to ignore anyON DELETE orON UPDATE clause used in the same statement.MATCH options do not have any other effect in MySQL, which in effect enforcesMATCH SIMPLE semantics full-time.

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.

You can obtain information about foreign keys from the Information SchemaKEY_COLUMN_USAGE table. An example of a query against this table is shown here:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME     > FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE     > WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;+--------------+---------------+-------------+-----------------+| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | CONSTRAINT_NAME |+--------------+---------------+-------------+-----------------+| fk1          | myuser        | myuser_id   | f               || fk1          | product_order | customer_id | f2              || fk1          | product_order | product_id  | f1              |+--------------+---------------+-------------+-----------------+3 rows in set (0.01 sec)

Information about foreign keys onInnoDB tables can also be found in theINNODB_SYS_FOREIGN andINNODB_SYS_FOREIGN_COLS tables, in theINFORMATION_SCHEMA database.

InnoDB andNDB tables support foreign keys.