EC
1986
MySQLERRORCommonSchemaHIGH confidence

Key reference and table reference do not match

Production Risk

Low — schema operation is rejected.

What this means

The columns listed in a foreign key definition do not correspond correctly to the columns in the referenced table. The foreign key declaration names a different table than the one actually referenced.

Why it happens
  1. 1FOREIGN KEY references a table that differs from the one named after REFERENCES.
  2. 2Typo in the table name within the FK constraint.
  3. 3Multiple-table foreign key definition where references are inconsistent.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE child (id INT, FOREIGN KEY (id) REFERENCES wrong_table(id));

expected output

ERROR 1986 (HY000): Key reference and table reference don't match.

Fix

Correct the table name in the REFERENCES clause

Correct the table name in the REFERENCES clause
CREATE TABLE child (id INT, FOREIGN KEY (id) REFERENCES parent(id));

Why this works

The REFERENCES table must match the intended parent table.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1986 ER_KEY_REF_DO_NOT_MATCH_TABLE_REF

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

← All MySQL errors