EC
1397
MariaDBerrorddlhigh confidence

Incorrect foreign key definition for referenced table

Production Risk

Low — DDL fails; no data affected.

What this means

A FOREIGN KEY definition is syntactically incorrect, often due to SET DEFAULT reference action which InnoDB does not support.

Why it happens
  1. 1Using ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT with InnoDB
  2. 2Incorrect number of columns in the FK vs the referenced index
How to reproduce
trigger — this will error
trigger — this will error
ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES ref(id) ON DELETE SET DEFAULT;

expected output

ERROR 1397 (HY000): Incorrect foreign key definition for 'fk': Key reference and table reference don't match

Fix

Use supported reference actions

Use supported reference actions
FOREIGN KEY (col) REFERENCES ref(id) ON DELETE SET NULL ON UPDATE CASCADE;

Why this works

InnoDB supports RESTRICT, CASCADE, SET NULL, and NO ACTION.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1397 ER_WRONG_FK_DEF

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

← All MariaDB errors