EC
1005
MySQLERRORCommonDDLHIGH confidence

Can't create table — usually a FK constraint error

Production Risk

Medium — partial schema creation leaves the database in an inconsistent state.

What this means

ER_CANT_CREATE_TABLE (1005, SQLSTATE HY000) is returned when CREATE TABLE fails. The most common cause is a foreign key constraint misconfiguration — type mismatch, missing index on referenced column, or referencing a non-existent table.

Why it happens
  1. 1Foreign key references a non-existent table or a column with a type mismatch
  2. 2The referenced column has no index (InnoDB requires an index on the referenced column)
  3. 3Storage engine does not support foreign keys (e.g. MyISAM silently ignores FK definitions)
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE orders (
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES no_such_table(id)
);

expected output

ERROR 1005 (HY000): Can't create table `mydb`.`orders` (errno: 150 "Foreign key constraint is incorrectly formed")

Fix

Run SHOW ENGINE INNODB STATUS to diagnose errno 150

WHEN Errno 150 appears alongside 1005.

Run SHOW ENGINE INNODB STATUS to diagnose errno 150
SHOW ENGINE INNODB STATUS\G
-- Look for: LATEST FOREIGN KEY ERROR
-- Then fix the referenced table/column:
CREATE TABLE customers (id INT PRIMARY KEY);
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

Why this works

SHOW ENGINE INNODB STATUS gives the exact foreign key error details that the top-level 1005 message omits.

What not to do

✕ Use MyISAM for tables with FK constraints

MyISAM accepts the FOREIGN KEY syntax but ignores it entirely — no constraint is enforced and you get false confidence in data integrity.

Sources
Official documentation ↗

MySQL 8.0 — 1005 ER_CANT_CREATE_TABLE

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors