Home Dashboard Directory Help

Foreign Key references view column by Jim Bennett


 as Won't Fix Help for as Won't Fix

Sign in
to vote
Type: Suggestion
ID: 352700
Opened: 6/24/2008 8:46:48 AM
Access Restriction: Public


I would find it beneficial if I could create a foreign key constraint on a table that would reference a column in a view
Sign in to post a comment.
Posted by Geoff Fane on 6/1/2014 at 8:46 PM
As MS has now deprecated SSRS Report Models having a security trimmed view that confirms to a single tables referential integrity would be absolutely lovely. Think about user navigation in Power Query with Fact and Dimension tables, if MS allowed reference foreign and primary keys to make use of those table constraints in a view you would effectively have a semantic view that was able to be navigated or filtered. Although the work to traverse the index would be more complex it wouldn't need to store an entire copy of the data in a second pre-filtered table. It would also be significantly faster than doing joins in client tools such as Power Query. Perhaps limit the view to columns and keys on a single table.
Posted by Nathan Lowry on 3/4/2013 at 11:25 AM
I can explain why I want to do this:

I have a list of all local government agencies registered with the State. This table includes counties, cities, metropolitan districts, etc.

I have another table that requires the CountyName to be included in every record. I could create foreign key constraint on the table such that CountyName were only obtained from a query (view) on the first table where only records for local agencies that are counties are selected.

But instead, I have to copy out the values of the query into another table in order to enforce the foreign key constraint - creating redundancy and increasing propensity for error.
Posted by Microsoft on 1/25/2012 at 11:21 AM
Dear Jim,

Thank you for submitting this suggestion, but given its priority relative to the many other enhancements we are considering, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Thank you,
Eric Hanson
Program Manager
SQL Server Query Processing
Posted by J.Moreno on 1/12/2012 at 1:31 PM
Basically this wouuld be useful whenever you have a table that should only reference a subset of the rows in another table. You could create a view that has just the subset, and then reference it as your FK, then the second table is guaranteed not only a valid reference but that the refeference meets the appropriate constraints. Simple example would be an address table with shipping, mailing, and billing addresses. Your shipment table can have a reference to a view of the address table that only includes shipping addresses guaranteeing that you have both a valid address and that the address isn't a billing address or mailing address.

It would work just as well if you could simply reference a unique index instead of either a view or table.
Posted by Jim Bennett on 6/27/2008 at 10:40 AM
I have a database that is structured in an EAV (Entity Attribute Value) design. As an example I have one code table that has fields like the following:
======== ========= ==============
PROV            1                 NEWFOUNDLAND
PROV            2                 PRINCE EDWARD ISLAND
STATUS         1                 ACTIVE
STATUS         2                 DORMANT
I would like to be able to create individual views for each different type of CODE_TYPE and use them as Foreign Key constraints for fields in other tables.
I have been searching the net on this topic and I think ORACLE has implemented something along these lines.
Posted by Microsoft on 6/26/2008 at 1:15 PM
This is interesting feedback. Thanks! Can you explain why you would want to do this? It is important for us to understand the scenario you'd use this in to design the feature right.

Sign in to post a workaround.