Menu
In SQL Server 2008 and given
TableA(A_ID, A_Data) TableB(B_ID, B_Data) ViewC(A_or_B_ID, A_or_B_Data) is it possible to define TableZ(A_or_B_ID, Z_Data) such that Z.A_or_B_ID column is constrained to the values found in ViewC? Can this be done with a foreign key against the view?
638
In the strict sense of the word, no you cannot set foreign keys on views.
FOREIGN KEY constraints can reference another column in the same table, and is referred to as a self-reference. A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.
A foreign key can refer to either a unique or a primary key of the parent table. If the foreign key refers to a non-primary unique key, you must specify the column names of the key explicitly.
You can't reference a view in a foreign key.
78
In older SQL Server editions foreign keys were possible only through triggers. You can mimic a custom foreign key by creating an Insert trigger which checks whether the inserted value appears in one of the relevant tables as well.
38
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
