Can I have a foreign key referencing a column in a view in SQL Server?
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.
If you really need A_or_B_ID
in TableZ, you have two similar options:
1) Add nullable A_ID
and B_ID
columns to table z, make A_or_B_ID
a computed column using ISNULL on these two columns, and add a CHECK constraint such that only one of A_ID
or B_ID
is not null
2) Add a TableName column to table z, constrained to contain either A or B. now create A_ID
and B_ID
as computed columns, which are only non-null when their appropriate table is named (using CASE expression). Make them persisted too
In both cases, you now have A_ID
and B_ID
columns which can have appropriate foreignkeys to the base tables. The difference is in which columns are computed. Also, youdon't need TableName in option 2 above if the domains of the 2 ID columns don'toverlap - so long as your case expression can determine which domain A_or_B_ID
falls into
(Thanks to comment for fixing my formatting)