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

Pour les autres qui trouvent cette entrée SO via Google : Assurez-vous que vous n'essayez pas d'effectuer une action SET NULL sur une colonne de clé étrangère (à être) définie comme "NOT NULL". Cela m'a causé une grande frustration jusqu'à ce que je me souvienne de faire un CHECK ENGINE INNODB STATUS.

Answer 2

Ce n'est certainement pas le cas, mais j'ai trouvé cette erreur assez courante et peu évidente. La cible d'un FOREIGN KEY pourrait ne pas être PRIMARY KEY . La réponse qui m'a été utile est la suivante :

Une FOREIGN KEY doit toujours être pointée vers un champ vrai PRIMARY KEY d'une autre table.

CREATE TABLE users(
   id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40));

CREATE TABLE userroles(
   id INT AUTO_INCREMENT PRIMARY KEY,
   user_id INT NOT NULL,
   FOREIGN KEY(user_id) REFERENCES users(id));

Answer 3

Comme indiqué par @andrewdotn, le meilleur moyen est de voir l'erreur détaillée( SHOW ENGINE INNODB STATUS; ) au lieu d'un simple code d'erreur.

L'une des raisons pourrait être qu'un index existe déjà avec le même nom, peut-être dans une autre table. En pratique, je recommande de préfixer le nom de la table avant le nom de l'index pour éviter de telles collisions. Par exemple, au lieu de idx_userId utiliser idx_userActionMapping_userId .

Answer 4

Assurez-vous d'abord que

1. vous utilisez des tables InnoDB.
2. Le champ pour FOREIGN KEY a le même type et la même longueur ( !) que le champ source.

J'ai eu le même problème et je l'ai résolu. J'avais un INT non signé pour un champ et juste un entier pour l'autre champ.

Answer 5

Conseil utile, utilisez SHOW WARNINGS; après avoir essayé votre CREATE et vous recevrez l'erreur ainsi que l'avertissement plus détaillé :

    ---------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                 |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+
| Warning |  150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error   | 1005 | Can't create table 'exampleTable' (errno:150)                                                                                                                                                                           |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+

Alors dans ce cas, il est temps de recréer ma table !