Can I have a foreign key referencing a column in a

问题:

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?

回答1:

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

回答2:

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.

回答3:

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)

回答4:

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

回答5:

There is another option. Treat TableA and TableB as subclasses of a new table called TablePrime. Adjust TableB's ID values so they do not coincide with TableA's ID values. Make the ID in TablePrime the PK and insert all of TableA's and TableB's (adjusted) IDs into TablePrime. Make TableA and TableB have FK relationships on their PK to the same ID in TablePrime.

You now have the supertype/subtype pattern, and can make constraints to TablePrime (when you want either-A-or-B) or one of the individual tables (when you want only A or only B).

If you need more details please ask. There are variations that will let you make sure A and B are mutually exclusive, or maybe the thing you're working with can be both at the same time. It's best to formalize that in the FKs if possible.

回答6:

It is easier to add a constraint that references a user defined function that makes the check for you, fCheckIfValueExists(columnValue) which returns true if the value exists and false if it doesn't.

The upside is that it can receive multiple columns, perform calculations with them, accept nulls and accept values that don't precisely correspond to a primary key or compare with results of joins.

Downside is that the optimizer can not use all his foreign key tricks.

回答7:

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!