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

Question

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?

Etty

Answer 1

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

Isochor

is this a limitation of SQL server or is it an unreasonable thing to want? – Such

@Brian I too would be interested in knowing if this is a limitation of a SQL Server or an unreasonable thing to want because at this point I'm about to emulate a view using triggers just to get FK support (though I'm using MySql). – Monroemonroy

This is a good answer to these follow up questions - #3833650 – Marucci

I'm not sure how thats a good answer to those questions...it's about a different DBMS and says that views were designed for hiding schema details and user convenience. Firstly, ok...but this wouldn't be the first thing ever finding solid use cases beyond initial design. Secondly, I'm not sure why an FK wouldn't do that. A view can be any query it doesn't even have to draw out of a table, it can be a bunch of constants unioned together...a foreign key seems pretty darn sensible in that case. If there's a reason why not I'd hope for something deeper. – Departed

Answer 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.

Cuspidor

welcome to StackOverflow. I found value in your answer since provides a workaround but the correct answer is the accepted one, and the question is more than 4 years old, so I'm just not voting but didn't want to left without this comment. – Gargantua

Answer 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)

Madison

Put words with underscores in back-ticks: A_or_B_ID – Agram

I'm working on adding some features to a legacy system, and this is a great way to patch old and new together. Thank you! – Sightread

Answer 4

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

Derrickderriey

Answer 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.

Hendrika

Answer 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.

Cliff

Downside is that the optimizer can not use all his foreign key tricks... ...and that the function will be run for every row you insert/update (so not too nice for sets). – Adapter

Answer 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!

Ranjiv

this question is about sql server – Departed

Answer 8

If you need the foreign keys for a reporting tool (like Power BI) then there is a workaround.

1. rename all views to from v_xxx to z_xxx, like sp_rename 'dbo.v_20002', 'z_2000';
2. for each view create a table v_xxx with no data: select * into v_xxx from z_xxx where 1=0
3. add foreign keys, like: ALTER TABLE v_2000 ADD FOREIGN KEY (crid) REFERENCES v_crid(crid)
4. open power bi and load v_xxx tables. foreign keys should apear under Model > Manage relationships
5. delete all v_xxx tables
6. rename all views to from z_xxx to v_xxx, like sp_rename 'dbo.z_20002', 'v_2000';
7. refresh power bi, foreign keys should remain under Manage relationships

Pyrethrin