是否可以在 SQLServer 中使用外键引用视图中的列？

在 SQLServer2008中，并给定

TableA(A_ID, A_Data)
TableB(B_ID, B_Data)
ViewC(A_or_B_ID, A_or_B_Data)

有没有可能定义 TableZ(A_or_B_ID, Z_Data)使得 Z.A_or_B_ID列只限于 ViewC中的值？可以使用外键对视图进行此操作吗？

73199 次浏览
小开
最佳答案

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

小开

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

小开

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)

小开

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.

小开

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.

小开

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.

小开

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!


提交答案