EC
3104
MySQLERRORNotableSchemaHIGH confidence

Invalid ON UPDATE/DELETE option for foreign key on generated column

Production Risk

Low — DDL fails; no data is affected.

What this means

A foreign key on a generated column was defined with an ON UPDATE or ON DELETE action that is incompatible with generated columns.

Why it happens
  1. 1Using ON UPDATE SET NULL or ON DELETE SET NULL for a NOT NULL generated column.
  2. 2Using ON UPDATE SET DEFAULT for a generated column.
How to reproduce
trigger — this will error
trigger — this will error
ALTER TABLE t ADD FOREIGN KEY (gen_col) REFERENCES other(id) ON UPDATE SET NULL;

expected output

ERROR 3104 (HY000): Column 'gen_col' cannot have a foreign key with SET NULL because it cannot be set to NULL.

Fix

Use ON UPDATE/DELETE RESTRICT or CASCADE instead

Use ON UPDATE/DELETE RESTRICT or CASCADE instead
ALTER TABLE t ADD FOREIGN KEY (gen_col) REFERENCES other(id) ON DELETE CASCADE;

Why this works

CASCADE and RESTRICT are compatible with generated columns.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 3104 ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN2

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

← All MySQL errors