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

As pointed by @andrewdotn the best way is to see the detailed error(SHOW ENGINE INNODB STATUS;) instead of just an error code.

One of the reasons could be that an index already exists with the same name, may be in another table. As a practice, I recommend prefixing table name before the index name to avoid such collisions. e.g. instead of idx_userId use idx_userActionMapping_userId.

Answer 2

In my case it was due to the fact that the field that was a foreign key field had a too long name, ie. foreign key (some_other_table_with_long_name_id). Try sth shorter. Error message is a bit misleading in that case.

Also, as @Jon mentioned earlier - field definitions have to be the same (watch out for unsigned subtype).

Answer 3

For people who are viewing this thread with the same problem:

There are a lot of reasons for getting errors like this. For a fairly complete list of causes and solutions of foreign key errors in MySQL (including those discussed here), check out this link:

MySQL Foreign Key Errors and Errno 150

Answer 4

When the foraign key constraint is based on varchar type, then in addition to the list provided by marv-el the target column must have an unique constraint.

Answer 5

I had the same problem with ALTER TABLE ADD FOREIGN KEY.

After an hour, I found that these conditions must be satisfied to not get error 150:

1. The Parent table must exist before you define a foreign key to reference it. You must define the tables in the right order: Parent table first, then the Child table. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with ALTER TABLE.

2. The two tables must both support foreign key constraints, i.e. ENGINE=InnoDB. Other storage engines silently ignore foreign key definitions, so they return no error or warning, but the FK constraint is not saved.

3. The referenced columns in the Parent table must be the left-most columns of a key. Best if the key in the Parent is PRIMARY KEY or UNIQUE KEY.

4. The FK definition must reference the PK column(s) in the same order as the PK definition. For example, if the FK REFERENCES Parent(a,b,c) then the Parent's PK must not be defined on columns in order (a,c,b).

5. The PK column(s) in the Parent table must be the same data type as the FK column(s) in the Child table. For example, if a PK column in the Parent table is UNSIGNED, be sure to define UNSIGNED for the corresponding column in the Child table field.

   Exception: length of strings may be different. For example, VARCHAR(10) can reference VARCHAR(20) or vice versa.

6. Any string-type FK column(s) must have the same character set and collation as the corresponding PK column(s).

7. If there is data already in the Child table, every value in the FK column(s) must match a value in the Parent table PK column(s). Check this with a query like:

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

   This must return zero (0) unmatched values. Obviously, this query is an generic example; you must substitute your table names and column names.

8. Neither the Parent table nor the Child table can be a TEMPORARY table.

9. Neither the Parent table nor the Child table can be a PARTITIONED table.

10. If you declare a FK with the ON DELETE SET NULL option, then the FK column(s) must be nullable.

11. If you declare a constraint name for a foreign key, the constraint name must be unique in the whole schema, not only in the table in which the constraint is defined. Two tables may not have their own constraint with the same name.

Hope this helps.

Answer 6

Please make sure at first that

1. you are using InnoDB tables.
2. field for FOREIGN KEY has the same type and length (!) as source field.

I had the same trouble and I've fixed it. I had unsigned INT for one field and just integer for other field.