Erreur MySQL 150 : Dépannage des contraintes de clé étrangère lors de la création d'une table

Question

J'essaie de créer une table dans MySQL avec 2 clés étrangères qui font référence à des clés primaires dans 2 autres tables, mais j'obtiens une erreur errno: 150 et la table ne sera pas créée.

Voici le SQL pour les 3 tables :

CREATE TABLE role_groups (
  `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
  `name` varchar(20),
  `description` varchar(200),
  PRIMARY KEY (`role_group_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50),
  `description` varchar(200),
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB;

create table role_map (
  `role_map_id` int not null `auto_increment`,
  `role_id` int not null,
  `role_group_id` int not null,
  primary key(`role_map_id`),
  foreign key(`role_id`) references roles(`role_id`),
  foreign key(`role_group_id`) references role_groups(`role_group_id`)
) engine=InnoDB;

Answer 1

Ces conditions doivent être remplies pour éviter l'erreur 150 lorsque ALTER TABLE ADD FOREIGN KEY :

1. La table parent doit exister avant qu'une clé étrangère puisse être définie pour la référencer. Vous devez définir les tables dans le bon ordre : table parent d'abord, puis table enfant. Si deux tables se référencent mutuellement, vous devez créer une table sans la contrainte FK, puis créer la deuxième table, puis utiliser ALTER TABLE pour ajouter la contrainte FK à la première table.

2. Les deux tables doivent prendre en charge les contraintes de clé étrangère, c'est-à-dire ENGINE=InnoDB. D'autres moteurs de stockage ignorent silencieusement les définitions de clés étrangères, de sorte qu'ils ne renvoient pas d'erreurs ou d'avertissements, mais n'enregistrent pas les contraintes FK.

3. La colonne référencée dans la table parent doit être la colonne la plus à gauche de la clé. Mieux encore, si la clé du parent est PRIMARY KEY 或 UNIQUE KEY.

4. Les définitions FK doivent faire référence aux colonnes PK dans le même ordre que les définitions PK. Par exemple, si l'ordre de FK REFERENCES Parent(a,b,c)，则不得按 (a,c,b) définit le PK du Parent sur les colonnes.

5. La colonne PK de la table parent doit avoir le même type de données que la colonne FK de la table enfant. Par exemple, si la colonne PK de la table parent est UNSIGNED，请务必为子表字段中的相应列定义 UNSIGNED.

   Exception : les cordes peuvent varier en longueur. Par exemple, VARCHAR(10) 可以引用 VARCHAR(20) et vice versa.

6. Toute colonne FK de type chaîne doit avoir le même jeu de caractères et le même classement que la colonne PK correspondante.

7. S'il y a déjà des données dans la table enfant, chaque valeur de la colonne FK doit correspondre à une valeur de la colonne PK de la table parent. Vérifiez ceci en utilisant une requête comme celle-ci :

   SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK 
    WHERE Parent.PK IS NULL;

   Cela doit renvoyer zéro (0) valeurs sans correspondance. Évidemment, cette requête est un exemple générique ; vous devez remplacer les noms de table et de colonne.

8. Ni la table parent ni la table enfant ne peuvent être des TEMPORARY tables.

9. Ni la table parent ni la table enfant ne peuvent être des tables PARTITIONNÉES.

10. Si un FK est déclaré à l'aide de l'option ON DELETE SET NULL, la colonne FK doit être nullable.

11. Si vous déclarez un nom de contrainte pour une clé étrangère, le nom de la contrainte doit être unique dans l'ensemble du schéma, pas seulement dans la table dans laquelle la contrainte est définie. Deux tables ne peuvent pas avoir leurs propres contraintes portant le même nom.

12. S'il y a d'autres FK dans d'autres tables pointant vers le même champ pour lequel vous essayez de créer un nouveau FK, et qu'ils sont mal formés (c'est-à-dire un classement différent), vous devrez d'abord les rendre cohérents. Cela peut être dû à un changement passé où SET FOREIGN_KEY_CHECKS = 0; a été incorrectement défini comme une relation incohérente. Voir la réponse de @andrewdotn ci-dessous pour obtenir des instructions sur la façon d'identifier ces FK problématiques.