Comment réparer l'erreur MySQL ne peut pas ajouter de contrainte de clé étrangère ?

Comment corriger l'erreur « MySQL ne peut pas ajouter de contrainte de clé étrangère » ? Vous pouvez recevoir le message d'erreur suivant lorsque vous tentez d'ajouter une contrainte de clé étrangère à une table MySQL : " ERREUR 1215 (HY000): Impossible d'ajouter une contrainte de clé étrangère ".

Une erreur comme celle-ci est très générale et ne fournit pas de détails spécifiques concernant la raison spécifique pour laquelle MySQL ne peut pas insérer une contrainte de clé étrangère dans une table. Pour résoudre ce problème, vous devrez identifier la cause exacte du problème.

Cet article explique la raison de l'erreur "#1215 - Impossible d'ajouter une contrainte de clé étrangère" de MySQL, comment trouver la raison et comment y remédier avec quatre méthodes efficaces.

Quelle est la raison de l'erreur MySQL "#1215 - Impossible d'ajouter une contrainte de clé étrangère" ?

Cette erreur peut avoir diverses causes. Afin de déterminer la cause de cette erreur, il est préférable d'examiner la section LATEST FOREIGN KEY ERROR du SHOW ENGINE INNODB STATUS.

De cette façon, vous aurez une meilleure compréhension du problème. Vous pourrez identifier votre cas dans la liste suivante :

Généralement, lors du chargement des vidages, la table ou l'index auquel la contrainte fait référence n'existe pas

Parfois, la table de base de données que les clients souhaitent ajouter aux contraintes n'existe pas, ce qui entraîne un message d'erreur 1215.

Cependant, rien n'indique que la table n'existe pas, il est donc conseillé d'utiliser la commande pour déterminer quelles tables sont disponibles dans la base de données.

Exécutez SHOW TABLES ou SHOW CREATE TABLE sur chacune des tables parent. Toute table qui renvoie une erreur 1146 indique que les tables ont été créées dans un ordre incorrect.

La solution consiste à exécuter le CREATE TABLE manquant et à réessayer ou à désactiver temporairement les vérifications de clé étrangère. Il est indispensable d'effectuer cette étape lors des restaurations de sauvegarde dans lesquelles des références circulaires peuvent être présentes.

Exécutez le code suivant pour corriger l'erreur :

 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS=0; SOURCE /backups/mydump.sql; -- restore your backup within THIS session SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

Tables ou index dans les guillemets d'abus de référence de contrainte

Si vous souhaitez diagnostiquer cette erreur, inspectez chaque déclaration FOREIGN KEY et assurez-vous qu'il n'y a pas de guillemets autour des qualificateurs d'objet ou qu'il y a des guillemets autour de la table et une paire autour de chaque nom de colonne.

Cela peut être résolu en ne citant rien ou en citant le tableau et la colonne séparément.

La clé locale, la table étrangère ou la colonne dans les références de contrainte contient une faute de frappe

Il est possible de diagnostiquer cette erreur en exécutant SHOW TABLES et SHOW COLUMNS et en comparant les chaînes à celles spécifiées dans votre déclaration REFERENCES. La faute de frappe doit être corrigée dès qu'elle est découverte.

La colonne à laquelle la contrainte fait référence n'est pas du même type ou de la même largeur que la colonne étrangère

Pour le diagnostic, utilisez le parent SHOW CREATE TABLE pour vous assurer que la colonne locale et la colonne référencée contiennent le même type de données et la même largeur.

Le problème peut être résolu en modifiant votre instruction DDL pour vous assurer que la définition de colonne dans la table enfant correspond à celle de la table parent.

L'objet étranger n'est pas une clé d'aucune sorte

Pour diagnostiquer ce problème, utilisez le parent SHOW CREATE TABLE pour examiner la partie REFERENCES de la table afin de vous assurer que la colonne qui y pointe n'est pas indexée.

Résolvez le problème en faisant de la colonne une CLÉ, une CLÉ UNIQUE ou une CLÉ PRIMAIRE.

La clé étrangère est un PK ou UK multi-colonnes, où la colonne référencée n'est pas la plus à gauche

Afin de diagnostiquer cette erreur, exécutez la commande parent SHOW CREATE TABLE pour déterminer si la partie REFERENCES pointe vers une colonne qui est présente dans certains index multi-colonnes mais qui n'est pas la plus à gauche dans sa définition.

Vous pouvez résoudre le problème en ajoutant un index à la table parent où la colonne référencée est la colonne la plus à gauche ou la seule.

Différents charsets/collations entre les deux tables/colonnes

Pour diagnostiquer le problème, exécutez SHOW CREATE TABLE parent et vérifiez que les parties CHARACTER SET et COLLATE de la colonne enfant correspondent à celles de la table parent.

Pour résoudre ce problème, assurez-vous que la table enfant DDL correspond au jeu de caractères et au classement de sa table/colonne parent, ou ALTER la table parent afin que sa définition corresponde à la définition de l'enfant.

La table parent n'utilise pas InnoDB

Le problème peut être diagnostiqué en exécutant SHOW CREATE TABLE parent et en vérifiant ENGINE=INNODB.

ALTER la table parent pour passer de MySQL à InnoDB pour résoudre ce problème.

Comment trouver la raison de l'erreur "Impossible d'ajouter une contrainte de clé étrangère" ?

Cette erreur n'indique pas la raison spécifique de l'incapacité de MySQL à implémenter une contrainte de clé étrangère sur la table, ce qui est très général.

Pour aider à résoudre ce problème, vous devrez identifier la cause exacte. Pour ce faire, exécutez la requête suivante dans votre client MySQL :

 SHOW ENGINE INNODB STATUS;

Trois champs doivent être renvoyés à partir de cette requête : Type, Nom et Statut. La DERNIÈRE ERREUR DE CLÉ ÉTRANGÈRE peut être identifiée en développant la colonne d'état du résultat.

Cette section vise à identifier pourquoi la dernière requête alter a échoué afin que vous puissiez résoudre le problème.

Il y a généralement une incompatibilité entre le type des colonnes dans la table primaire et la table étrangère comme cause de ce problème.

Le problème peut également être causé par une incompatibilité entre les types de moteurs de deux tables, tels que MyISAM et InnoDB.

Cela est également possible en raison de différents classements entre les tables, l'une utilisant UTF-8 et l'autre utilisant latin1.

Vous devez prêter attention aux facteurs suivants :

Moteur : Chaque table doit être configurée avec le même moteur de base de données, par exemple, InnoDB.

Type de données : dans une table, les deux colonnes doivent avoir le même " type de datatype. int(11) datatype. int(11) ", alors que, dans un autre, smallint(5) posera un problème.

Collation : Afin d'assurer la compatibilité, les jeux de caractères des deux colonnes doivent être identiques, par exemple, UTF8.

Attention : les colonnes peuvent toujours en avoir une différente, même si vos tables ont le même classement.

Unique : les clés étrangères doivent faire référence à des champs uniques dans la table de référence, généralement des champs primaires.

Ungisned : Il est possible qu'une table ait des clés non signées et qu'une autre n'en ait pas.

Comment réparer l'erreur MySQL "#1215 - Impossible d'ajouter une contrainte de clé étrangère" (4 méthodes) ?

Après avoir discuté des différentes causes de ce message d'erreur, nous sommes passés à l'étape suivante. Cette erreur doit être corrigée, alors voyons comment le faire avec quatre méthodes efficaces.

Méthode 1 : vérifier la disponibilité de la table

Pour commencer à résoudre l'erreur, vous pouvez vérifier si la table existe en exécutant la commande suivante :

 show tables;

Il ressort de ce code que la contrainte a pu être créée puisque la table était disponible.

Méthode 2 : Assurez-vous que la table parent utilise InnoDB

Une enquête plus approfondie peut être menée en vérifiant le moteur de stockage défini sur la table de base de données. La commande suivante peut être utilisée pour cela :

 SHOW CREATE TABLE parent

Le résultat est que vous constaterez qu'InnoDB n'est pas le moteur de stockage. Comme ENGINE=MYISAM a été défini, vous pouvez modifier le moteur de stockage en exécutant la commande suivante :

 ALTER TABLE parent ENGINE=INNODB;

Il est enfin possible de corriger l'erreur avec cette méthode.

Méthode 3 : Vérifiez s'il y a des citations inappropriées

Pour chaque déclaration FOREIGN KEY, assurez-vous qu'aucun guillemet n'est inséré autour des qualificateurs d'objet ou que des guillemets séparés entourent les noms de table et de colonne.

Avec cette méthode, il faut soit ne rien citer, soit citer la colonne et la table séparément pour corriger l'erreur.

Méthode 4 : vérifier si la clé existe ou non

Lors de la saisie des commandes MySQL, il est nécessaire de s'assurer que la commande correcte est saisie. La vérification croisée garantit que le client ne fait pas référence à une clé de table inexistante dans la table cible.

Par conséquent, il est nécessaire de corriger les clients sur la façon d'utiliser la clé sur la table. Il est possible que la colonne utilisée pour ON DELETE SET NULL n'ait pas de définition nulle dans certains cas.

Pour éviter l'erreur MySQL 1215, vous devez vous assurer que la colonne est définie sur la valeur nulle par défaut.

Conclusion

Cet article a expliqué la raison de l'erreur "#1215 - Impossible d'ajouter une contrainte de clé étrangère" de MySQL, comment trouver la cause et comment y remédier avec quatre méthodes efficaces.

Merci d'avoir pris le temps de revoir cet article. Il nous fait plaisir de vous le présenter. Dans la section des commentaires, s'il vous plaît poster des questions ou des commentaires.

Veuillez aimer et suivre nos pages Facebook et Twitter si vous souhaitez rester au courant des dernières nouvelles.