Rechercher dans le manuel MySQL

13.1.20.6 Using FOREIGN KEY Constraints

Table des matières

MySQL 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 essential syntax for a foreign key constraint definition in a CREATE TABLE or ALTER TABLE statement looks like this:

  1. [CONSTRAINT [symbol]] FOREIGN KEY
  2.     [index_name] (col_name, ...)
  3.     REFERENCES tbl_name (col_name,...)
  4.     [ON DELETE reference_option]
  5.     [ON UPDATE reference_option]
  6.  
  7. reference_option:
  8.     RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

index_name represents a foreign key ID. The index_name value is ignored if there is already an explicitly defined index on the child table that can support the foreign key. Otherwise, MySQL implicitly creates a foreign key index that is named according to the following rules:

  • If defined, the CONSTRAINT symbol value is used. Otherwise, the FOREIGN KEY index_name value is used.

  • If neither a CONSTRAINT symbol or FOREIGN KEY index_name is defined, the foreign key index name is generated using the name of the referencing foreign key column.

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 use the same storage engine. They must not be TEMPORARY tables.

    In MySQL 8.0, creation of a foreign key constraint requires the REFERENCES privilege for the parent table.

  • Corresponding columns in the foreign key and the referenced key must have similar data types. 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.

  • When foreign_key_checks is enabled, which is the default setting, character set conversion is not permitted on tables that include a character string column used in a foreign key constraint. The workaround is described in Section 13.1.9, “ALTER TABLE Syntax”.

  • MySQL 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 dropped later, 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 the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 15.6.2.1, “Clustered and Secondary Indexes”).

    NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key.

  • 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, if used, must be unique in the database. A duplicate symbol will result in an error similar to: ERROR 1022 (2300): Can't write; duplicate key in table '#sql- 464_1'. If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, MySQL uses the foreign key index name up to MySQL 8.0.15, and automatically generates a constraint name thereafter.

  • InnoDB does not currently support foreign keys for tables with user-defined partitioning. This includes both parent and child tables.

    This restriction does not apply for NDB tables that are partitioned by KEY or LINEAR KEY (the only user partitioning types supported by the NDB storage engine); these may have foreign key references or be the targets of such references.

  • For NDB tables, ON UPDATE CASCADE is not supported where the reference is to the parent table's primary key.

Additional aspects of FOREIGN KEY constraint usage are described under the following topics in this section:

Referential Actions

This section describes how foreign keys help guarantee referential integrity.

For storage engines supporting foreign keys, MySQL 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.

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. MySQL supports five options regarding the action to be taken, listed here:

  • 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, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

    If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables a parent and child, an ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be defined for the other in order for cascading operations to succeed. If an ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail with an error.

    Note

    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. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

    If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

  • RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

  • NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

  • SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

For an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT.

MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, “child table records” really refers to dependent records within the same table.

A foreign key constraint on a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions.

A foreign key constraint on the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.

A foreign key constraint cannot reference a virtual generated column.

For InnoDB restrictions related to foreign keys and generated columns, see Section 15.6.1.5, “InnoDB and FOREIGN KEY Constraints”.

Table des matières Haut

Examples of Foreign Key Clauses

Here is a simple example that relates parent and child tables through a single-column foreign key:

  1. CREATE TABLE parent (
  2.     id INT NOT NULL,
  3.     PRIMARY KEY (id)
  4. ) ENGINE=INNODB;
  5.  
  6. CREATE TABLE child (
  7.     id INT,
  8.     parent_id INT,
  9.     INDEX par_ind (parent_id),
  10.     FOREIGN KEY (parent_id)
  11.         REFERENCES parent(id)
  12.         ON DELETE CASCADE
  13. ) 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:

  1. CREATE TABLE product (
  2.     category INT NOT NULL, id INT NOT NULL,
  3.     price DECIMAL,
  4.     PRIMARY KEY(category, id)
  5. )   ENGINE=INNODB;
  6.  
  7. CREATE TABLE customer (
  8.     id INT NOT NULL,
  9.     PRIMARY KEY (id)
  10. )   ENGINE=INNODB;
  11.  
  12. CREATE TABLE product_order (
  13.     no INT NOT NULL AUTO_INCREMENT,
  14.     product_category INT NOT NULL,
  15.     product_id INT NOT NULL,
  16.     customer_id INT NOT NULL,
  17.  
  18.     PRIMARY KEY(no),
  19.     INDEX (product_category, product_id),
  20.     INDEX (customer_id),
  21.  
  22.     FOREIGN KEY (product_category, product_id)
  23.       REFERENCES product(category, id)
  24.       ON UPDATE CASCADE ON DELETE RESTRICT,
  25.  
  26.     FOREIGN KEY (customer_id)
  27.       REFERENCES customer(id)
  28. )   ENGINE=INNODB;

Table des matières Haut

Adding Foreign Keys

You can add a new foreign key constraint to an existing table by using ALTER TABLE. The syntax relating to foreign keys for this statement is shown here:

  1. ALTER TABLE tbl_name
  2.     ADD [CONSTRAINT [symbol]] FOREIGN KEY
  3.     [index_name] (col_name, ...)
  4.     REFERENCES tbl_name (col_name,...)
  5.     [ON DELETE reference_option]
  6.     [ON UPDATE reference_option]

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.

Table des matières Haut

Dropping Foreign Keys

You can also use ALTER TABLE to drop foreign keys, using the syntax shown here:

  1. 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 generated internally when the foreign key is created. To find out the symbol value when you want to drop a foreign key, use a SHOW CREATE TABLE statement, as shown here:

  1. mysql> SHOW CREATE TABLE ibtest11c\G
  2. *************************** 1. row ***************************
  3.        Table: ibtest11c
  4. Create Table: CREATE TABLE `ibtest11c` (
  5.   `A` int(11) NOT NULL auto_increment,
  6.   `D` int(11) NOT NULL default '0',
  7.   `B` varchar(200) NOT NULL default '',
  8.   `C` varchar(175) default NULL,
  9.   PRIMARY KEY  (`A`,`D`,`B`),
  10.   KEY `B` (`B`,`C`),
  11.   KEY `C` (`C`),
  12.   CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
  13. REFERENCES `ibtest11a` (`A`, `D`)
  14. ON DELETE CASCADE ON UPDATE CASCADE,
  15.   CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
  16. REFERENCES `ibtest11a` (`B`, `C`)
  17. ON DELETE CASCADE ON UPDATE CASCADE
  18. ) ENGINE=INNODB CHARSET=utf8mb4
  19. 1 row in set (0.01 sec)
  20.  
  21. mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;

Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE but is unsupported for ALTER TABLE ... ALGORITHM=COPY.

Table des matières Haut

Foreign Keys and Other MySQL Statements

Table and column identifiers in a FOREIGN KEY ... REFERENCES ... clause can be quoted within backticks (`). Alternatively, double quotation marks (") can be used if the ANSI_QUOTES SQL mode is enabled. The setting of the lower_case_table_names system variable is also taken into account.

You can view a child table's foreign key definitions as part of the output of the SHOW CREATE TABLE statement:

  1. SHOW CREATE TABLE tbl_name;

You can also obtain information about foreign keys by querying the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table.

You can find information about foreign keys used by InnoDB tables in the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables, also in the INFORMATION_SCHEMA database.

mysqldump produces correct definitions of tables in the dump file, including the foreign keys for child tables.

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:

  1. mysql> SET foreign_key_checks = 0;
  2. mysql> SOURCE dump_file_name;
  3. 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, MySQL does not permit the creation of a foreign key constraint where a column references a nonmatching column type. Also, if a table has foreign key constraints, ALTER TABLE cannot be used to alter the table to use another storage engine. To change the storage engine, you must drop any foreign key constraints first.

You cannot issue DROP TABLE for a table that is referenced by a FOREIGN KEY constraint, unless you do SET foreign_key_checks = 0. When you drop a table, any constraints that were defined in the statement used to create that table 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 correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails due to Error 150, this means that a foreign key definition would be incorrectly formed for the altered table.

For InnoDB tables, you can obtain a detailed explanation of the most recent InnoDB foreign key error in the MySQL Server, by checking the output of SHOW ENGINE INNODB STATUS.

MySQL extends metadata locks, as necessary, to tables that are related by a foreign key constraint. Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables. This feature also enables updates to foreign key metadata when a parent table is modified. In earlier MySQL releases, foreign key metadata, which is owned by the child table, could not be updated safely.

If a table is locked explicitly with LOCK TABLES, any tables related by a foreign key constraint are opened and locked implicitly. For foreign key checks, a shared read-only lock (LOCK TABLES READ) is taken on related tables. For cascading updates, a shared-nothing write lock (LOCK TABLES WRITE) is taken on related tables that are involved in the operation.

Table des matières Haut

Foreign Keys and the ANSI/ISO SQL Standard

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.

The 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. MySQL 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 requires that the referenced columns be indexed for performance reasons. 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 UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.

Furthermore, MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

Table des matières Haut

Foreign Key Metadata

The INFORMATION_SCHEMA.KEY_COLUMN_USAGE table identifies the key columns that have constraints. Metadata specific to InnoDB foreign keys is found in the INNODB_SYS_FOREIGN and INNODB_SYS_FOREIGN_COLS tables.

Table des matières Haut

Foreign Key Errors

In the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), information about the most recent InnoDB foreign key error can be obtained by checking SHOW ENGINE INNODB STATUS output.

Warning

If a user has table-level privileges for all parent tables, ER_NO_REFERENCED_ROW_2 and ER_ROW_IS_REFERENCED_2 error messages for foreign key operations expose information about parent tables. If a user does not have table-level privileges for all parent tables, more generic error messages are displayed instead (ER_NO_REFERENCED_ROW and ER_ROW_IS_REFERENCED).

An exception is that, for stored programs defined to execute with DEFINER privileges, the user against which privileges are assessed is the user in the program DEFINER clause, not the invoking user. If that user has table-level parent table privileges, parent table information is still displayed. In this case, it is the responsibility of the stored program creator to hide the information by including appropriate condition handlers.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-create-table-foreign-keys.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut