The error appears when a foreign key is declared on a column that a STORED generated column depends on, which MySQL disallows.
MySQL error ER_CANNOT_ADD_FOREIGN_BASE_COL_STORED means the server blocked a foreign key because the target column is used by a STORED generated column; move the constraint to another column or convert the STORED column to VIRTUAL to resolve the issue.
ER_CANNOT_ADD_FOREIGN_BASE_COL_STORED
MySQL throws this error when you attempt to add a foreign key constraint to a column that is the base column for a STORED generated column in the same table. Starting with MySQL 5.7.14, the server blocks this operation because it could break dependency tracking between the base column and the generated column.
The restriction applies during CREATE TABLE, ALTER TABLE ADD FOREIGN KEY, and when restoring dumps. MySQL immediately stops the statement and returns SQL state HY000 along with error code 3192.
The primary cause is a design conflict: a STORED generated column persists its value on disk and relies on one or more base columns. Defining a foreign key on any of those base columns complicates update ordering and referential integrity checks, so MySQL disallows the combination altogether.
The error often surfaces during automated migrations where an ORM first adds STORED generated columns and then blindly adds foreign keys, unaware of the limitation introduced in MySQL 5.7.14.
Move the foreign key to a different column that is not referenced by a STORED generated column. If referential integrity is needed on the generated value, place the foreign key on the generated column itself, provided it is deterministic and indexed.
You can also convert the STORED generated column to a VIRTUAL generated column or replace it with a normal column updated by triggers, then reattempt the foreign key creation. As a final option, drop the STORED generated column before adding the constraint.
Scenario: A table has a STORED generated column full_id built from tenant_id and id, and you need a foreign key on tenant_id. Solution: Change full_id to VIRTUAL, or create the foreign key on full_id instead.
Scenario: A migration script adds STORED columns and foreign keys in the same statement. Solution: Split the migration into two steps and add the foreign key only after the STORED column issue is resolved.
Keep columns that participate in STORED generated columns separate from those that require foreign keys. Prefer VIRTUAL generated columns unless physical storage is essential. Test schema changes on staging databases running the same MySQL version as production.
Using Galaxy, you can run schema diffs in the editor and review generated DDL to catch conflicting statements early, leveraging version control to prevent surprises in CI/CD pipelines.
Error 1822 - ER_CANNOT_ADD_FOREIGN: Generic foreign key creation failure, often thrown when other constraints or indexes are missing.
Error 3106 - ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN: Raised when a foreign key targets a generated column that is not indexed or deterministic.
A STORED generated column depends on the target column, so MySQL blocks the foreign key to protect dependency integrity.
An automated migration tool adds foreign keys after introducing STORED generated columns without detecting version limitations.
A developer converts a VIRTUAL generated column to STORED and later attempts to add a foreign key on one of its base columns.
Dump-and-restore operations executed against a newer MySQL release clash with legacy DDL that violates the rule.
Generic failure when adding a foreign key, usually due to missing indexes or column mismatches.
Occurs when a foreign key references a generated column with invalid properties.
High level error that wraps foreign key issues encountered during CREATE TABLE.
No, the restriction is hard coded from MySQL 5.7.14 onward. You must change the schema design instead.
No, VIRTUAL generated columns do not persist data on disk, so MySQL allows foreign keys on their base columns.
No, MySQL 8.0 keeps the same rule because the underlying dependency issues still apply.
Galaxy surfaces schema diffs in its editor, letting you spot STORED and foreign key conflicts before running migrations in production.
.avif)
.avif){kind=logo}
