EC
1240
MySQLERRORNotableSchema / ConstraintsHIGH confidence

Key reference and table reference do not match

Production Risk

Low — DDL fails; no data is affected.

What this means

ER_KEY_REF_DO_NOT_MATCH_TABLE_REF (1240, SQLSTATE HY000) is raised when a foreign key references a table but the specified key columns do not correspond to any index on the referenced table.

Why it happens
  1. 1Foreign key references columns that are not indexed (no primary key or unique index)
  2. 2The referenced column names are incorrect or misspelled
How to reproduce
trigger — this will error
trigger — this will error
-- parent.name has no unique index
CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_name VARCHAR(100),
  FOREIGN KEY (parent_name) REFERENCES parent(name)  -- name is not indexed
);

expected output

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

Fix

Add an index on the referenced column

Add an index on the referenced column
ALTER TABLE parent ADD UNIQUE INDEX (name);
-- Then recreate the foreign key

Why this works

Foreign keys must reference an indexed column (primary key or unique key) on the parent table.

Sources
Official documentation ↗

MySQL 8.0 — 1240 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