MySQL Creating tables with Foreign Keys giving err

Question

I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I am getting an errno: 150 error and it will not create the table.

Here is the SQL for all 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;

Any help would be greatly appreciated.

Answer 1

I encountered the same problem, but I check find that I hadn't the parent table. So I just edit the parent migration in front of the child migration. Just do it.

Answer 2

This is usually happening when you try to source file into existing database. Drop all the tables first (or the DB itself). And then source file with SET foreign_key_checks = 0; at the beginning and SET foreign_key_checks = 1; at the end.

Answer 3

For others that find this SO entry via Google: Be sure that you aren't trying to do a SET NULL action on a foreign key (to be) column defined as "NOT NULL." That caused great frustration until I remembered to do a CHECK ENGINE INNODB STATUS.

Answer 4

Make sure that the foreign keys are not listed as unique in the parent. I had this same problem and I solved it by demarcating it as not unique.

Answer 5

Make sure that the properties of the two fields you are trying to link with a constraint are exactly the same.

Often, the 'unsigned' property on an ID column will catch you out.

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

Answer 6

What's the current state of your database when you run this script? Is it completely empty? Your SQL runs fine for me when creating a database from scratch, but errno 150 usually has to do with dropping & recreating tables that are part of a foreign key. I'm getting the feeling you're not working with a 100% fresh and new database.

If you're erroring out when "source"-ing your SQL file, you should be able to run the command "SHOW ENGINE INNODB STATUS" from the MySQL prompt immediately after the "source" command to see more detailed error info.

You may want to check out the manual entry too:

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message. If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.

— MySQL 5.1 reference manual.