How to Fix MySQL Error 1005: Can't Create Table (Foreign Key Constraint Error)

MySQL error 1005 (ER_CANT_CREATE_TABLE) surfaces as ERROR 1005 (HY000): Can't create table '<db>.<table>' (errno: 150 "Foreign key constraint is incorrectly formed") — or occasionally errno 168 for NDB clusters. The SQLSTATE is HY000 (general error). Despite its generic name, this error is almost exclusively a foreign key constraint problem when using InnoDB.

What This Error Means

MySQL raises ER_CANT_CREATE_TABLE when the storage engine rejects the CREATE TABLE statement before the table is written to disk. The error is a wrapper — the real detail lives in the errno value embedded in the message. Errno 150 means InnoDB's foreign key validation logic found a structural problem with a FOREIGN KEY clause in the DDL statement.

InnoDB validates foreign keys at table-creation time, not lazily. When you issue CREATE TABLE, the engine checks every FOREIGN KEY constraint against the referenced table immediately. If the referenced table doesn't exist yet, the data types don't match exactly, the referenced columns lack a suitable index, or the storage engines differ, InnoDB refuses to create the table and rolls back the entire statement.

The connection remains open after the error — no transaction is left open — but the table was not created. You can query SHOW ENGINE INNODB STATUS\G immediately after the error to see a human-readable explanation of exactly which constraint failed and why, under the LATEST FOREIGN KEY ERROR section.

Common Causes

1. Mismatched column data types. The foreign key column and the referenced column must have identical types, including sign. A BIGINT UNSIGNED column cannot reference a BIGINT (signed) column. Length differences in VARCHAR also matter on some character sets.

2. Missing index on the referenced column. InnoDB requires that the referenced column(s) be covered by a primary key or an index whose leading columns match the foreign key columns exactly. A unique index or any index where the FK columns are the leftmost prefix will satisfy this.

3. Referenced table doesn't exist yet. In a migration or schema dump, tables are often created in the wrong order. If orders references customers but customers hasn't been created yet, the FK fails.

4. Storage engine mismatch. InnoDB enforces foreign keys; MyISAM silently ignores them. If the child table is InnoDB and the parent table is MyISAM (or vice versa), InnoDB will reject the foreign key constraint.

5. Different character set or collation. On VARCHAR columns, the character set and collation must match between the child and parent column. A utf8mb4_unicode_ci column cannot reliably reference a utf8mb4_bin column.

6. SET DEFAULT referential action with InnoDB. InnoDB does not support ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT. Using either will trigger errno 150.

7. Circular or self-referential FK with wrong definition. Self-referential foreign keys are valid, but the column being referenced must still have an index.

How to Fix ER_CANT_CREATE_TABLE

1. Read the InnoDB status output. This is the fastest path to the real cause:

   SHOW ENGINE INNODB STATUS\G
   

   Look for the LATEST FOREIGN KEY ERROR section. It will tell you the exact constraint name, the problematic column, and the reason (e.g., "Trying to add to index PRIMARY tuple... but the table does not have that index").

2. Ensure data types match exactly, including sign.

   -- Parent table
   CREATE TABLE customers (
     id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
   );
   
   -- Child table: id column MUST also be BIGINT UNSIGNED
   CREATE TABLE orders (
     id     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     cust_id BIGINT UNSIGNED NOT NULL,
     FOREIGN KEY (cust_id) REFERENCES customers(id)
   );
   

3. Add an index to the referenced column if missing.

   -- If the parent column lacks an index, add one:
   ALTER TABLE customers ADD INDEX idx_customers_id (id);
   

   For a primary key this is never needed, but for non-PK references it is mandatory.

4. Create parent tables before child tables. In migration scripts, order matters. Either sort your CREATE TABLE statements so referenced tables come first, or temporarily disable FK checks during bulk schema loads:

   SET FOREIGN_KEY_CHECKS = 0;
   
   -- Run all CREATE TABLE statements in any order
   
   SET FOREIGN_KEY_CHECKS = 1;
   

   Re-enabling FK checks does not retroactively validate existing data, so only use this approach when the data is known-good or the tables are empty.

5. Make sure both tables use InnoDB.

   -- Verify engine of existing table:
   SHOW CREATE TABLE customers\G
   
   -- Convert if necessary:
   ALTER TABLE customers ENGINE = InnoDB;
   

6. Fix collation mismatches on VARCHAR columns.

   -- Both columns must share charset + collation:
   CREATE TABLE customers (
     id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL PRIMARY KEY
   );
   
   CREATE TABLE orders (
     cust_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
     FOREIGN KEY (cust_id) REFERENCES customers(id)
   );
   

7. Replace SET DEFAULT with a supported action.

   -- Not supported in InnoDB:
   -- FOREIGN KEY (cust_id) REFERENCES customers(id) ON DELETE SET DEFAULT
   
   -- Use a supported action instead:
   FOREIGN KEY (cust_id) REFERENCES customers(id) ON DELETE SET NULL
   -- or ON DELETE CASCADE, ON DELETE RESTRICT, ON DELETE NO ACTION
   

Additional Information

• The errno embedded in the error message is key: errno 150 is the standard InnoDB foreign key formation error; errno 168 appears in MySQL Cluster (NDB) for unsupported features.
• SHOW ENGINE INNODB STATUS was introduced very early (MySQL 4.0) and remains the most reliable diagnostic tool for FK errors.
• MySQL 8.0 improved error messaging slightly — in some cases you will see a more descriptive message alongside the traditional errno 150 text.
• Related SQLSTATE codes: 42S01 (table already exists, raised separately by ER_TABLE_EXISTS_ERROR); HY000 covers a wide range of general engine errors.
• ORMs like Hibernate, ActiveRecord, and SQLAlchemy will typically surface this as a generic database exception wrapping the original message. The original MySQL error text and errno are usually available on the inner exception — always log or inspect the root cause rather than just the ORM wrapper.
• In Liquibase and Flyway migrations, FK errors during CREATE TABLE will abort the changeset. Check your migration ordering if running schema bootstraps.

Frequently Asked Questions

Why does the same CREATE TABLE work on one server but fail on another? Most likely the two servers have different default storage engines or different character set defaults. If the default engine is MyISAM on one server, parent tables might end up as MyISAM (which accepts FKs silently without enforcing them), while InnoDB on the other server correctly rejects the malformed constraint. Check SHOW VARIABLES LIKE 'default_storage_engine' and SHOW VARIABLES LIKE 'character_set_database' on both.

I set FOREIGN_KEY_CHECKS = 0 but still get error 1005 — why? FOREIGN_KEY_CHECKS = 0 suppresses foreign key validation at DML time and during ALTER TABLE, but InnoDB still validates the syntax and structure of foreign key definitions at CREATE TABLE time (e.g., it will still reject SET DEFAULT referential actions). It does not suppress errno 150 for syntactically invalid constraint definitions.

How do I find which column is causing the problem? Run SHOW ENGINE INNODB STATUS\G immediately after the error. The LATEST FOREIGN KEY ERROR section shows the exact constraint, the column, and the reason. Alternatively, run SHOW WARNINGS right after the failed CREATE TABLE — MySQL sometimes emits a second warning with more detail.

Does this error ever have nothing to do with foreign keys? Rarely. Non-FK causes include trying to create a table in a tablespace that is full or read-only (typically on embedded/NDB deployments), or hitting OS-level file creation limits. In those cases the errno value is different from 150 (e.g., errno 13 for permissions). The message format is the same but the errno in parentheses tells you which category of problem you're dealing with.