Can I add a foreign key constraint in MYSQL when the referenced table is actually a view?
Based on the following I might wonder that a table and a view are considered different formats https://stackoverflow.com/a/31183407/1342636
Seems to me this is not allowed, but I have not seen any which actually states it is disallowed.
For a field to be defined as a foreign key, the referenced parent field must have an index defined on it.
As per documentation on foreign key constraints:
REFERENCES parent_tbl_name (index_col_name,...)
As VIEWs are virtual tables, all its fields are also virtual.
And defining of index is not supported on virtual fields.
As per documentation on Restrictions on Views:
It is not possible to create an index on a view.
And hence you can't use a virtual table, i.e. view, as a referenced parent table (which does not support indexes) to define and map a foreign key to create a child table.
Example:
