MySQL Reference Manual :: 16.7.4 Contraintes de clés étrangères FOREIGN KEY

Depuis la version 3.23.43b, InnoDB respecte les contraintes de clé étrangères.

La syntaxe des définitions de contraintes de clés étrangères de InnoDB est la suivante :

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

Les deux tables doivent être de type InnoDB, dans la table, il doit y avoir un INDEX où les clés étrangères sont listées comme première colonne, dans le même ordre, et dans la table référencée, il doit y avoir un INDEX où les colonnes référencées sont listées comme premières colonnes, dans le même ordre. Les préfixes d'index ne sont pas supportés pour les clés de contrainte.

InnoDB ne crée pas automatiquement les index nécessaires pour les clés étrangères : vous devez ls créer vous-même. Les index sont nécessaires pour accélérer les vérifications de contrainte, et éviter un scan de table.

Les colonnes correspondantes de la contrainte dans la table et la table de référence doivent avoir le même type, pour éviter les conversions lors des comparaisons. La taille et la présente du signe pour les entiers doit être les mêmes. La taille des chaînes doivent être les mêmes. Si vous spécifiez une action SET NULL, assurez vous que vous n'avez pas déclaré les colonnes de la table fille NOT NULL.

Si MySQL vous retourne une numéro d'erreur 1005 lors de la comande CREATE TABLE, et un message d'erreur de numéro 150, alors la création de la table a échoué à cause de la contrainte de clé étrangère, qui n'a pas été correctement formulée. Similairement, si une commande ALTER TABLE échoue et indique une erreur 150, c'est que la définition de la clé étrangère est incorrectement formulée dans la table modifiée. Depuis la version 4.0.13, vous pouvez utiliser la commande SHOW INNODB STATUS pour avoir une explication détaillée de la dernière erreur de clé étrangère InnoDB sur le serveur.

Depuis la version 3.23.50, InnoDB ne vérifie pas la clé étrangère pour les clés étrangères ou les clés référencées qui contienent des valeurs NULL.

Une entorse aux standards : si dans la table parente, il y a plusieurs lignes qui ont la même valeur de clé référencée, alors InnoDB effectue les vérifications de clé étrangères comme si les autres parents avec la même valeur de clé n'existaient pas. Par exemple, si vous avez défini une contrainte de type RESTRICT et qu'il y a une ligne fille avec plusieurs lignes parente, InnoDB n'acceptera pas l'effacement d'aucun des parents.

Depuis la version 3.23.50, vous pouvez aussi associer la clause ON DELETE CASCADE ou ON DELETE SET NULL avec la contrainte de clé étrangère. Les options correspondante ON UPDATE sont disponibles depuis la version 4.0.8. Si ON DELETE CASCADE est spécifiée, et qu'une ligne de la table parente est effacée, alors InnoDB va automatiquement effacer toute les lignes qui sont dans la table fille et dont les valeurs de clé étrangère sont celles référencées dans la ligne parente. Si ON DELETE SET NULL est spécifiée, les lignes filles sont automatiquement modifiée pour que la colonne de la clé étrangère prenne la valeur de NULL.

Une entorse aux standards : si ON UPDATE CASCADE ou ON UPDATE SET NULL cascade récursivement jusqu'à la même table, elle agira comme pour un RESTRICT. Cela est fait pour éviter les boucles infinies des modifications en cascade. Une clause ON DELETE SET NULL auto-référente, d'un autre coté, fonctionne depuis la version 4.0.13. La clause ON DELETE CASCADE auto-référente à toujours fonctionné.

Un exemle :

CREATE TABLE parent(id INT NOT NULL,
                    PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) TYPE=INNODB;

Voici un exemple plus complexe où la table product_order a des clés étrangères sur deux tables. La première clé est un index à deux colonnes, dans la table product. Les autres clés sont mono-colonnes, dans la table customer :

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) TYPE=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)) TYPE=INNODB;

Depuis la version 3.23.50, InnoDB vous permet d'ajouter une nouvelle clé à une table, grâce à la syntaxe

ALTER TABLE yourtablename
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

N'oubliez pas de commencer par créer les index nécessaires en premier!. Vous pouvez aussi ajouter des clés étrangères reflexives, en utilisant la commande ALTER TABLE.

Depuis la version 4.0.13, InnoDB supporte la syntaxe ALTER TABLE pour supprimer une clé étrangère :

ALTER TABLE yourtablename
    DROP FOREIGN KEY fk_symbol

Si la clause FOREIGN KEY inclut un nom de contraite CONSTRAINT lors de la création, vous pouvez utiliser ce nom pour effacer la clé. Les contraintes peuvent porter un nom depuis MySQL 4.0.18. SInon, la valeur fk_symbol est généré en interne par InnoDB lorsque la clé étrangère est créée. Pour savoir quel symbole utiliser pour effacer une clé étrangère, utilisez la commande SHOW CREATE TABLE. Par exemple :

mysql> 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
) TYPE=InnoDB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

Depuis MySQL version 3.23.50, l'analyseur InnoDB autorise l'utilisation des guillemets obliques autour des noms de tables et colonnes dans une clause FOREIGN KEY ... REFERENCES .... Depuis MySQL 4.0.5, l'analyseur InnoDB prend aussi en compte la variable système lower_case_table_names.

Dans InnoDB en versions < 3.23.50, ALTER TABLE et CREATE INDEX ne doivent pas être utilisé avec des tables qui ont des contraintes de clés étrangères, ou qui sont référencées dans des clés étrangères : une commande ALTER TABLE supprime toutes les clés étrangères qui sont définies pour cette table. Vous ne devriez pas utiliser ALTER TABLE sur la table référencée, mais utiliser DROP TABLE puis CREATE TABLE pour modifier le schéma. Lorsque MySQL exécute la commande ALTER TABLE, il risque d'utiliser en interne la commande RENAME TABLE, et cela va poser des problèmes pour les clés étrangères qui reposent sur cette table. Une commande CREATE INDEX est traitée par MySQL comme une commande ALTER TABLE, et ces restrictions s'appliquent aussi.

Lorsqu'il vérifie les clés étrangères, InnoDB pose des verrous de lignes partagées sur les lignes des tables qu'il utilise. InnoDB vérifie immédiatement les contraintes de clés étrangères : la vérification n'attend pas la validation de la transaction.

Si vous voulez ignorer les contraintes de clés étrangères durant, par exemple, une opération de LOAD DATA, vous pouvez utiliser la commande SET FOREIGN_KEY_CHECKS=0.

InnoDB vous permet d'effacer n'importe quelle table, même si cela va casser les contraintes de clés étranègres qui référence cette table. Lorsque vous supprimez une table, la contrainte de clé étrangère qui y était attachée est aussi supprimée.

Si vous recréez une table qui a été supprimée, sa définition doit se conformer aux contraintes des clés étrangères qui la référencent. Elle doit avoir les bons types et noms de colonnes, et doit avoir les bonnes clés, comme indiqué ci-dessus. Si ces contraintes ne sont pas vérifiées, MySQL vous gratifiera d'une erreur 1005, et vous enverra lire le message numéro 150.

Depuis la version 3.23.50 InnoDB retourne la définition de clé étrangère lorsque vous utilisez la commande

SHOW CREATE TABLE tbl_name;

De plus, mysqldump produit aussi les définitions correctes de tables, sans oublier les clés étrangères.

Vous pouvez aussi lister les clés étrangères d'une table T avec

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'

Les contraintes de clés étragnères sont listées dans les commentaires de la table.

Lors des vérifications des contraintes, InnoDB pose des verrous de lignes sur les lignes parents ou enfants qu'il utilise. InnoDB vérifie immédiatement les contraintes de clés : la vérification n'est pas reportée jusqu'à la validation de la transaction.

Pour simplifier l'importation de données dans des tables qui ont des contraintes, mysqldump ajoute automatiquement la commande qui met la variable FOREIGN_KEY_CHECKS à 0, depuis MySQL version 4.1.1. Cela évite des problèmes spécifiques avec les tables qui doivent être chargées dans un ordre particulier. Pour les versions antérieures, vous pouvez désactiver manuellement la variable depuis mysql lors du chargement du fichier comme ceci :

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

Cela vous permet de faire l'importation des données des tables dans n'importe quel ordre. Cela accélère aussi l'opération d'importation. FOREIGN_KEY_CHECKS est disponible depuis MySQL 3.23.52 et 4.0.3.

Mettre FOREIGN_KEY_CHECKS à 0 peut aussi être utile pour les opérations de LOAD DATA.

InnoDB permet l'effacement de n'importe quelle table, même si cela casse les contraintes de clés étrangères. Lorsque vous effacez une table, les contraintes définies sur cette table sont aussi effacées.

Si vous recréez une table qui a été effacée, elle doit avoir une définition qui est compatible avec les clés étrangères qui l'utilise. Elle doit avoir les bonnes colonnes et les index. Si cela n'est pas vrai, MySQL retourne une erreur 1005, et fait référence à un message d'erreur numéro 150.