EC
1239
MariaDBERRORNotableSchema / ConstraintsHIGH confidence

Incorrect foreign key definition

Production Risk

Low — DDL fails; no data is affected.

What this means

ER_WRONG_FK_DEF (1239, SQLSTATE 42000) is raised when a foreign key definition is syntactically or structurally incorrect, such as when the key columns do not match the referenced columns in count or order.

Why it happens
  1. 1The number of columns in the foreign key does not match the referenced primary/unique key
  2. 2Column order in the foreign key definition does not match the referenced key
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE child (
  id INT,
  parent_id INT,
  FOREIGN KEY (id, parent_id) REFERENCES parent(id)  -- 2 cols vs 1 col
);

expected output

ERROR 1239 (42000): Incorrect foreign key definition for 'child': Key reference and table reference don't match

Fix

Match the foreign key column count and order to the referenced key

Match the foreign key column count and order to the referenced key
CREATE TABLE child (
  id INT,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES parent(id)
);

Why this works

The foreign key columns must exactly match the referenced key columns in count, order, and compatible data types.

Sources
Official documentation ↗

MySQL 8.0 — 1239 ER_WRONG_FK_DEF

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

← All MariaDB errors