3102
MySQLERRORNotableSchemaHIGH confidence
Cannot add foreign key on virtual generated column
Production Risk
Low — DDL fails; no data is modified.
What this means
A foreign key cannot be added referencing or based on a virtual generated column.
Why it happens
- 1Attempting to create a foreign key where the child or parent column is a VIRTUAL generated column.
How to reproduce
trigger — this will error
trigger — this will error
ALTER TABLE t ADD FOREIGN KEY (virtual_gen_col) REFERENCES other(id);
expected output
ERROR 3102 (HY000): Cannot add foreign key on the base column of an indexed virtual column.
Fix
Use STORED generated columns instead of VIRTUAL for FK columns
Use STORED generated columns instead of VIRTUAL for FK columns
ALTER TABLE t MODIFY gen_col INT GENERATED ALWAYS AS (a + b) STORED;
Why this works
STORED generated columns have physical storage and can participate in foreign keys.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 3102 ER_CANNOT_ADD_FOREIGN_BASE_COL_VIRTUAL
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev