CODEWITHSUNDEEP
mysqlsqlinnodb
Mazeltov
Mazeltov

Reputation: 551

MySQL foreign key error 1005

I have this error when I try to create a table: Can't create table users_groups (errno: 150)

My script:

CREATE TABLE `users_groups` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `group_id` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_users_groups_users1_idx` (`user_id`),
  KEY `fk_users_groups_groups1_idx` (`group_id`),
  CONSTRAINT `uc_users_groups` UNIQUE (`user_id`, `group_id`),
  CONSTRAINT `fk_users_groups_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_groups_groups1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 1

Views: 303

Answers (1)

Didier Spezia
Didier Spezia

Reputation: 73226

Error 150 is related to the definition of foreign keys.

You did not mention the MySQL version, but here are a few things to check:

  • tables should exist, and their storage engine should all be InnoDB
  • referenced columns should exist
  • indexes on referenced columns should exist in the referenced tables
  • definition of the index (including column order) should match the definition of the foreign key
  • data types of referenced columns should be identical (including length, null values, etc ...)

The documentation of MySQL says it is possible to use the "SHOW ENGINE INNODB STATUS" command just after error 150 to get more information about the error itself. I suggest you try it. In the output, search for the 'LATEST FOREIGN KEY ERROR' section.

Upvotes: 1

Related Questions