Menu
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.
404
You can't reference a view in a foreign key.
Creating a new table with a foreign key requires CREATE TABLE permission in the database, and ALTER permission on the schema in which the table is being created. Creating a foreign key in an existing table requires ALTER permission on the table.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
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:
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
