Error Code: 3175. Cannot create index on virtual column whose base column has foreign constraint

Question

In MySQL 5.7.11, adding a composite index where one of the columns is a virtual generated column results in the following error:

Error Code: 3175. Cannot create index on virtual column whose base column has foreign constraint

In reality, the base columns from which the virtual column is generated do not have any foreign constraints, so the error message is inexplicable.

The error does not occur in MySQL 5.7.15.

Answer 1

The error message is wrong. The actual problem is that two of the other columns in the index have foreign keys defined on them which are set to on update cascade. This is not allowed prior to MySQL 5.7.14:

In MySQL 5.7.13 and earlier, InnoDB does not permit defining cascading referential actions on non-virtual foreign key columns that are explicitly included in a virtual index. This restriction is lifted in MySQL 5.7.14. (source)

Changing the keys to on update restrict makes the error go away. This also explains why this error does not occur in MySQL 5.7.15.