Foreign Key references view column - by Jim Bennett

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


16
2
Sign in
to vote
ID 352700 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 6/24/2008 8:46:48 AM
Access Restriction Public

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
Sign in to post a comment.
Posted by Jeremy Huppatz (Solitaire Systems) on 9/17/2014 at 1:40 AM
I actually have a very strong use case for being able to create a foreign key against a view. Let's say you're building a schema logging solution for a configuration management database, data architecture tool or similar. You want to create a table called ViewEntityReferences which enumerates the references to source data in a view, and that source data can either be a column-level function call, a column level aggregate function, a table, another view or a table-valued function call. So if we then have 5 types of reference, and various reference roles (e.g. primary data source, joined data source, union data source, CTE data source, etc..) as well as a reference index, then you might want to log:
[SourceObjectID]             A reference to the source object's ID key in the relevant entity type table.
[SourceObjectSchemaID] A reference to the source object's schema ID key in the relevant entity type table.
[Referencetype]                The reference type of the source object in the view.
[QueryRole]                     The role of the source object in the view.

However, if we want to constrain the [SourceObjectID] against all of the relevant entity type table (Relation, View, Scalar Function, Aggregate Function, Table-valued function) it would be nice to be able to present the foreign key as a union of the relevant entity tables. Assuming the use of newsequentialID as the default for each of the SourceObjectID values, they should be globally unique within the database. If we had a view that provided an abstraction of the SourceObjectID (along with a Source Object Type) along the lines of:
CREATE VIEW SourceObjects AS
SELECT TableID as SourceObjectID, SchemaID, 'Table' as ReferenceEntityType UNION
SELECT ViewID as SourceObjectID, SchemaID, 'View' as ReferenceEntityType UNION
SELECT FunctionID as SourceObjectID, SchemaID, 'Function' as ReferenceEntityType

Then it makes a lot of sense to want to declare a foreign key against the SourceObjectID view attribute in our EntityReferences table.

I can think of plenty of other situations where you might be aggregating an abstraction to a more generic reference via a view and want to enforce DRI to an appropriate set of tables, without having to have separate keys (each with its own FOREIGN KEY on a nullable column) in the EntityReferences table (one for each source table) and then having to mess about with some kind of COALESCE() operation to get the distinct key value and having to deal with nullable FK IDs.
Posted by MRSA on 7/15/2014 at 5:36 PM
I second Geoff's comments. We are exactly in the same situation and want to use Power Query on top of a database while maintaining row level security and being able to define relationships between views would be essential in this case otherwise users have to join views. With the departure of SSRS Report Model, MS BI stack is missing a semantic model that users can use to build simple list reports or data extracts and Power Query on top of database would have been the best choice.
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:
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