MySQL blocks adding a foreign key to a base column that already participates in an index on a virtual generated column.
ER_CANNOT_ADD_FOREIGN_BASE_COL_VIRTUAL occurs when you try to add a foreign key to a column that is part of an index created for a virtual generated column. Remove or adjust the virtual column index, or create the foreign key on a different column to resolve the issue.
ER_CANNOT_ADD_FOREIGN_BASE_COL_VIRTUAL
Error 3174 appears when a FOREIGN KEY clause references a column that is already covered by an index supporting a virtual generated column. MySQL 5.7.10 introduced this restriction to protect internal dependency ordering.
The server refuses the DDL statement, rolls back the alteration, and returns SQL state HY000 with the official message Cannot add foreign key on the base column of indexed virtual column.
The primary trigger is an attempt to create or alter a table so that a base column gains a foreign key while an index on a virtual column also uses that same base column. MySQL treats the column as already logically referenced and blocks the additional constraint.
The error is also raised when you import a dump or run pt-online-schema-change if the incoming DDL implicitly leads to the same conflict.
First, identify the virtual column and its index. Decide whether you can drop the index, drop the virtual column, or move the foreign key to a different column. Apply the safest option in a transaction on test data.
If the virtual column index is not required, drop it, then add the foreign key. If both are needed, re-architect the table by materializing the virtual column or splitting the data into two tables.
During database refactoring, engineers often add a foreign key to improve referential integrity. If analytics created a virtual JSON extractor column earlier, the new constraint collides, triggering 3174. Removing or materializing the virtual column solves the conflict.
Automated migration tools that generate separate ADD COLUMN, ADD INDEX, and ADD CONSTRAINT statements can hit the error in the middle of a migration. Re-order the steps so the foreign key is added before the virtual column index.
Document all generated columns and their supporting indexes. Review them before adding foreign keys. Run mysqldump --no-data to inspect hidden dependencies.
Use Galaxy's dependency-aware SQL editor to highlight virtual column indexes that share base columns with candidate foreign keys, preventing the error before you run the migration.
Error 1005 Cannot create table may surface when the underlying cause is 3174; review SHOW ENGINE INNODB STATUS. Error 3106 Cannot modify generated column can appear when you try to change the same base column definition. Resolve virtual column conflicts first.
A VIRTUAL column is defined with an expression that references the base column, and you added an index on that virtual column.
A tool adds the virtual column index first, then attempts to add the foreign key without checking dependencies.
Older code introduced generated columns that current developers are unaware of, causing unexpected conflicts when new foreign keys are introduced.
A generic table creation failure that often wraps lower level errors like 3174.
Occurs when altering a base column of a generated column without proper adjustments.
Raised when an index is attempted on an expression that InnoDB cannot optimize.
No. The rule was introduced in 5.7.10 and remains in all later versions, including 8.0.
No. The restriction is hardcoded in the InnoDB engine to protect referential integrity.
No. STORED columns write their results to disk, so the base column can take a foreign key safely.
Galaxy highlights virtual column dependencies as you type and warns when a foreign key targets a conflicting column, saving you from runtime errors.
.avif)
.avif){kind=logo}
