EC
1690
MariaDBERRORNotableDDLMEDIUM confidence

A required key is missing for table

Production Risk

Medium — DDL operation fails; no data is changed.

What this means

MySQL requires a key (index) that is referenced by a constraint or operation but that key does not exist on the table.

Why it happens
  1. 1Foreign key constraint references a column without an index.
  2. 2Attempting to add a foreign key to a column that lacks an index.
How to reproduce
trigger — this will error
trigger — this will error
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

expected output

ERROR 1690 (HY000): A required key is missing for table 'orders'.

Fix

Add an index on the foreign key column before adding the constraint

Add an index on the foreign key column before adding the constraint
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

Why this works

InnoDB requires an index on the referencing column to enforce foreign key constraints efficiently.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1690 ER_MISSING_KEY

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

← All MariaDB errors