Error Code: 1005. Can’t create table ‘…’ (errno: 150)

by

Error Code: 1005 — there is a wrong primary key reference in your code

Usually it’s due to a referenced foreign key field that does not exist. It might be you have a typo mistake, or check case it should be same, or there’s a field-type mismatch. Foreign key-linked fields must match definitions exactly.

Some known causes may be:

  1. The two key fields type and/or size doesn’t match exactly. For example, if one is INT(10) the key field needs to be INT as well and not BIGINT or SMALLINT or TINYINT. You should also check that one is not SIGNED and the other is UNSIGNED. They both need to be exactly the same.
  2. One of the key field that you are trying to reference does not have an index and/or is not a primary key. If one of the fields in the relationship is not a primary key, you must create an index for that field.
  3. The foreign key name is a duplicate of an already existing key. Check that the name of your foreign key is unique within your database. Just add a few random characters to the end of your key name to test for this.
  4. One or both of your tables is a MyISAM table. In order to use foreign keys, the tables must both be InnoDB. (Actually, if both tables are MyISAM then you won’t get an error message – it just won’t create the key.) In Query Browser, you can specify the table type.
  5. You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values.
  6. Make sure that the Charset and Collate options are the same both at the table level as well as individual field level for the key columns.
  7. You have a default value (that is, default=0) on your foreign key column
  8. One of the fields in the relationship is part of a combination (composite) key and does not have its own individual index. Even though the field has an index as part of the composite key, you must create a separate index for only that key field in order to use it in a constraint.
  9. You have a syntax error in your ALTER statement or you have mistyped one of the field names in the relationship
  10. The name of your foreign key exceeds the maximum length of 64 characters.

For more details, refer to: MySQL Error Number 1005 Can’t create table

More Related Contents:

  1. MySQL “ERROR 1005 (HY000): Can’t create table ‘foo.#sql-12c_4’ (errno: 150)”
  2. MySQL errorno 121
  3. MySQL Error 1215: Cannot add foreign key constraint
  4. Mysql error 1452 – Cannot add or update a child row: a foreign key constraint fails
  5. MySQL foreign key constraints, cascade delete
  6. How do I see all foreign keys to a table or column?
  7. MySQL Cannot Add Foreign Key Constraint
  8. Required to join 2 tables with their FKs in a 3rd table
  9. MySQL Foreign Key Error 1005 errno 150 primary key as foreign key
  10. Basics of Foreign Keys in MySQL?
  11. Can a foreign key reference a non-unique index?
  12. MySQL – Conditional Foreign Key Constraints
  13. How do I use on delete cascade in mysql?
  14. MySql Error 150 – Foreign keys
  15. Add Foreign Key to existing table
  16. How to change the foreign key referential action? (behavior)
  17. How to create relationships in MySQL
  18. MySQL InnoDB foreign key between different databases
  19. MySQL Removing Some Foreign keys
  20. Force InnoDB to recheck foreign keys on a table/tables?
  21. Is it possible to reference one column as multiple foreign keys?
  22. How to insert values in table with foreign key using MySQL?
  23. Meaning of “n:m” and “1:n” in database design
  24. MySQL foreign key to allow NULL?
  25. How to use django models with foreign keys in different DBs?
  26. How to remove constraints from my MySQL table?
  27. Foreign key constraints: When to use ON UPDATE and ON DELETE
  28. MySQL. Can’t create table errno 150
  29. MySQL DROP all tables, ignoring foreign keys
  30. MySQL Relationships

Leave a Comment