Home Dashboard Directory Help
Search

Foreign Key references view column by Jim Bennett


Status: 

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


13
2
Sign in
to vote
Type: Suggestion
ID: 352700
Opened: 6/24/2008 8:46:48 AM
Access Restriction: Public
0
Workaround(s)
view

Description

I would find it beneficial if I could create a foreign key constraint on a table that would reference a column in a view
Details
Sign in to post a comment.
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:
CODE_TYPE CODE_VALUE CODE_DESCRIPTION
======== ========= ==============
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.

Thanks,
Eric
Sign in to post a workaround.