Problem: I am trying to create a foreign key on a column to a view. The view was made as a SELECT * from another schema with a primary key constraint on the column I am using.
Is this possible? If this is, is this considered bad practice? If this isn't, is there an alternative? It doesn't seem to be possible through phpmyadmin, and I can't create a primary key on a view.
Reason why: I used two schemas for two different applications, but there is a 'user' table (not necessarily users in the mysql.user table) in one schema that I would like to reuse in the schema without granting permission to the other schema. This is why I used a view.
No. It is not possible.
Please see MySQL Forums post.
in the referenced table, there must be an index where the referenced columns are listed as the first columns ...
Views are materialized. They are not tables with left-most indexes on them natively. Views are also snapshots in time (the time of creation). They can fall out of favor of the current schema, unbeknownst until used the next time. So, for many reasons, they will not be candidates for FK use as the target, the referenced, as they call it.
From the Manual Page FAQ: Views
After a view has been created, it is possible to drop or alter a table or view to which the definition refers. To check a view definition for problems of this kind, use the CHECK TABLE statement.
Further reason why a view is a snapshot as I call it. Its validity at any point in time can become highly compromised. Not an FK referenced candidate.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments