EC
1985
MySQLERRORCommonSchemaHIGH confidence

Incorrect foreign key definition

Production Risk

Low — table creation or alteration is rejected.

What this means

A FOREIGN KEY constraint definition is syntactically or semantically incorrect. The referenced column list does not match the referencing column list in type, count, or ordering.

Why it happens
  1. 1Foreign key columns and referenced columns have different data types.
  2. 2Number of columns in the FK does not match the referenced columns.
  3. 3Referenced table does not have an index on the referenced columns.
  4. 4Using SET NULL on a NOT NULL column.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE orders (
  order_id INT,
  FOREIGN KEY (order_id) REFERENCES products(product_id, name)
);

expected output

ERROR 1985 (HY000): Incorrect foreign key definition for 'orders': Key reference and table reference don't match.

Fix 1

Ensure column counts and types match

Ensure column counts and types match
CREATE TABLE orders (
  order_id INT,
  FOREIGN KEY (order_id) REFERENCES products(product_id)
);

Why this works

Foreign key and referenced columns must have matching count and compatible types.

Fix 2

Add an index on the referenced column if missing

Add an index on the referenced column if missing
ALTER TABLE products ADD INDEX idx_product_id (product_id);

Why this works

InnoDB requires the referenced columns to be indexed.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1985 ER_WRONG_FK_DEF

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

← All MySQL errors