Can I have a foreign key referencing a column in a view in SQL Server?

marc_12260 0
Viewed 72120

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?

view foreign-keys sql-server
7 Answers

You can't reference a view in a foreign key.

Brian_Fisher_438160

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 foreign keys to the base tables. The difference is in which columns are computed. Also, you don't need TableName in option 2 above if the domains of the 2 ID columns don't overlap - so long as your case expression can determine which domain A_or_B_ID falls into

(Thanks to comment for fixing my formatting)

user_154980

Sorry, you cannot FK to a view in SQL Server.

Jarrett_Meyer_58340

Sorry, In the strict sense of the word, no you cannot set foreign keys on views. Here is why:

InnoDB is the only built-in storage engine for MySQL that features foreign keys. Any InnoDB table will be registered in information_schema.tables with engine = 'InnoDB'.

Views, while registered in information_schema.tables, has a NULL storage engine. There are no mechanisms in MySQL to have foreign keys on any table that has an undefined storage engine.

Thanks!

user_83148780
Related