MySQL Error 150: Troubleshooting foreign key constraints when creating table

Question

I'm trying to create a table in MySQL with two foreign keys that reference primary keys in 2 other tables, but I'm getting an errno: 150 error, and it The table will not be created.

The following 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;

Answer 1

These conditions must be met so that error 150 will not occur when ALTER TABLE ADD FOREIGN KEY:

1. The parent table must exist before a foreign key can be defined to reference it. You must define the tables in the correct order: parent table first, then child table. If two tables reference each other, you must create one table without the FK constraint, then create the second table, and then use ALTER TABLE to add the FK constraint to the first table.

2. Both tables must support foreign key constraints, that is, ENGINE=InnoDB. Other storage engines silently ignore foreign key definitions, so they do not return errors or warnings, but do not save FK constraints.

3. The column referenced in the parent table must be the leftmost column of the key. It's best if the key in the parent is PRIMARY KEY or UNIQUE KEY.

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

5. The PK column in the parent table must have the same data type as the FK column in the child table. For example, if the PK column in the parent table is UNSIGNED, be sure to define UNSIGNED for the corresponding column in the child table field.

   Exception: Strings may vary in length. For example, VARCHAR(10) can refer to VARCHAR(20) and vice versa.

6. Any FK column of type string must have the same character set and collation as the corresponding PK column.

7. If there is already data in the child table, each value in the FK column must match a value in the parent table's PK column. Check this using a query like this:

   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 a generic example; you must replace table 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 an FK is declared with the ON DELETE SET NULL option, the FK column must be nullable.

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

12. If there are any other FKs in other tables pointing to the same field that you are trying to create a new FK for, and they are malformed (i.e. different collation), you will need to make them consistent first. This may be due to a past change where SET FOREIGN_KEY_CHECKS = 0; was incorrectly defined as an inconsistent relationship. See @andrewdotn's answer below for instructions on how to identify these problematic FKs.