MySQL Création de tables avec des clés étrangères donnant errno : 150

Question

J'essaie de créer une table dans MySQL avec deux clés étrangères, qui font référence aux clés primaires de deux 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;

Toute aide serait grandement appréciée.

Answer 1

J'ai eu le même problème avec ALTER TABLE ADD FOREIGN KEY .

Après une heure, j'ai constaté que ces conditions doivent être satisfaites pour ne pas obtenir l'erreur 150 :

1. La table Parent doit exister avant que vous ne définissiez une clé étrangère pour la référencer. Vous devez définir les tables dans le bon ordre : D'abord la table Parent, puis la table Enfant. Si les deux tables se réfèrent l'une à l'autre, vous devez créer une table sans contrainte FK, puis créer la deuxième table, puis ajouter la contrainte FK à la première table avec ALTER TABLE .

2. Les deux tables doivent toutes deux supporter 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, ils ne renvoient donc aucune erreur ou avertissement, mais la contrainte FK n'est pas enregistrée.

3. Les colonnes référencées dans la table Parent doivent être les colonnes les plus à gauche d'une clé. C'est mieux si la clé dans le Parent est PRIMARY KEY o UNIQUE KEY .

4. La définition FK doit référencer la ou les colonnes PK dans le même ordre que la définition PK. Par exemple, si le FK REFERENCES Parent(a,b,c) alors le PK du parent ne doit pas être défini sur les colonnes afin de (a,c,b) .

5. La ou les colonnes PK de la table Parent doivent être du même type de données que la ou les colonnes FK de la table Enfant. Par exemple, si une colonne PK de la table Parent est UNSIGNED assurez-vous de définir UNSIGNED pour la colonne correspondante dans le champ de la table Enfant.

   Exception : la longueur des chaînes de caractères peut être différente. Par exemple, VARCHAR(10) peut faire référence VARCHAR(20) ou vice versa.

6. Toute colonne FK de type chaîne doit avoir le même jeu de caractères et la même collation que la ou les colonnes PK correspondantes.

7. S'il y a déjà des données dans la table Enfant, chaque valeur de la ou des colonnes FK doit correspondre à une valeur de la ou des colonnes PK de la table Parent. Vérifiez cela avec une requête comme :

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

   Il doit retourner zéro (0) valeur non appariée. Évidemment, cette requête est un exemple générique ; vous devez substituer vos noms de table et de colonne.

8. Ni la table Parent ni la table Enfant ne peuvent être une TEMPORARY table.

9. Ni la table Parent ni la table Enfant ne peuvent être une PARTITIONED table.

10. Si vous déclarez un FK avec l'option ON DELETE SET NULL l'option FK, alors la ou les colonnes FK doivent être annulables.

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, et pas seulement dans la table dans laquelle la contrainte est définie. Deux tables ne peuvent pas avoir leur propre contrainte avec le même nom.

12. S'il existe d'autres FK dans d'autres tables pointant vers le même champ que celui pour lequel vous tentez de créer le nouveau FK, et qu'ils sont mal formés (c'est-à-dire avec une collation différente), ils devront d'abord être mis en cohérence. Cela peut être le résultat de modifications antérieures où SET FOREIGN_KEY_CHECKS = 0; a été utilisé avec une relation incohérente définie par erreur. Voir la réponse de @andrewdotn ci-dessous pour savoir comment identifier ces FK problématiques.

J'espère que cela vous aidera.

Answer 2

Le message générique "errno 150" de MySQL " signifie qu'une contrainte de clé étrangère n'a pas été correctement formée ." Comme vous le savez probablement déjà si vous lisez cette page, le message d'erreur générique "errno : 150" est vraiment inutile. Cependant :

Vous pouvez obtenir le réel message d'erreur en exécutant SHOW ENGINE INNODB STATUS; et ensuite chercher LATEST FOREIGN KEY ERROR dans la sortie.

Par exemple, cette tentative de créer une contrainte de clé étrangère :

CREATE TABLE t1
(id INTEGER);

CREATE TABLE t2
(t1_id INTEGER,
 CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));

échoue avec l'erreur Can't create table 'test.t2' (errno: 150) . Cela ne dit rien d'utile à personne, sinon que c'est un problème de clé étrangère. Mais exécutez SHOW ENGINE INNODB STATUS; et il dira :

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

Il dit que le problème est qu'il ne trouve pas d'index. SHOW INDEX FROM t1 montre qu'il n'y a pas d'index du tout pour la table t1 . Fixez cela en définissant, par exemple, une clé primaire sur t1 et la contrainte de clé étrangère sera créée avec succès.

Answer 3

Assurez-vous que les propriétés des deux champs que vous essayez de lier avec une contrainte sont exactement les mêmes.

Souvent, la propriété "non signée" d'une colonne d'identification vous surprendra.

ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;

Answer 4

Quel est l'état actuel de votre base de données lorsque vous exécutez ce script ? Est-elle complètement vide ? Votre SQL fonctionne bien pour moi lors de la création d'une base de données à partir de zéro, mais errno 150 a généralement à voir avec l'abandon et la recréation de tables qui font partie d'une clé étrangère. J'ai l'impression que vous ne travaillez pas avec une base de données 100% fraîche et nouvelle.

Si vous rencontrez des erreurs lors de la création de votre fichier SQL, vous devriez pouvoir exécuter la commande "SHOW ENGINE INNODB STATUS" à partir de l'invite MySQL immédiatement après la commande "source" pour obtenir des informations plus détaillées sur les erreurs.

Vous pouvez aussi vérifier l'entrée manuelle :

Si vous recréez une table qui a été abandonnée, elle doit avoir une définition conforme aux contraintes de clé étrangère qui la référencent. Elle doit avoir les bons noms et types de colonnes, et elle doit avoir des index sur les clés référencées, comme indiqué précédemment. Si ces conditions ne sont pas remplies, MySQL renvoie le numéro d'erreur 1005 et fait référence à l'erreur 150 dans le message d'erreur. Si MySQL renvoie le numéro d'erreur 1005 à partir d'une instruction CREATE TABLE, et que le message d'erreur fait référence à l'erreur 150, la création de la table a échoué parce qu'une contrainte de clé étrangère n'était pas correctement formée.

- Manuel de référence de MySQL 5.1 .

Answer 5

Pour les personnes qui consultent ce fil de discussion avec le même problème :

Il y a beaucoup de raisons pour obtenir des erreurs comme celle-ci. Pour une liste assez complète des causes et solutions des erreurs de clé étrangère dans MySQL (y compris celles discutées ici), consultez ce lien :

Erreurs de clé étrangère MySQL et Errno 150