mysql - Supports transactions, row-level locking, and foreign keys

Question

For some reason I cannot create this table:

CREATE TABLE user_role (
  user_id VARCHAR(20) NOT NULL,
  role_id INTEGER UNSIGNED NOT NULL,

  FOREIGN KEY (user_id)
    REFERENCES users(user_id),
  FOREIGN KEY (role_id)
    REFERENCES roles(role_id)
);

The following similar table has no problems:

CREATE TABLE role_perm (
  role_id INTEGER UNSIGNED NOT NULL,
  perm_id INTEGER UNSIGNED NOT NULL,

  FOREIGN KEY (role_id)
    REFERENCES roles(role_id),
  FOREIGN KEY (perm_id)
    REFERENCES permissions(perm_id)
);

The error message I'm getting is:

#1005 - Can't create table 'test.user_role' (errno: 150) (Details...) Supports transactions, row-level locking, and foreign keys

Any ideas?

Answer 1

See http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

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 correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed.

If is rather difficult to guess as you didn't provide the definitions for the roles and permissions tables but to paraphrase the doc...

• ...in order to have a foreign key on a column, you must have an index on the "target" column.
• ...in order to have a foreign key on a column, both "source" and "target" columns must have the same type (incl. the same size if applicable).
• ...in order to have a foreign key on a column, both tables must use InnoDB engine.