I know that SQL Server doesn't support using a view as the reference for a foreign key constraint, is there a reason (i.e. something to do with the relational model) this is the case? It seems like it would be useful....
Asked
Active
Viewed 8,241 times
15
-
3I've asked a related question (not the why but how it could be done with other ways): Are there DBMS that allow a Foreign Key that References a View (and not only base tables)? It probably has to do with the complexity of implementing such a feature. The relational model is fine with such a concept. – ypercubeᵀᴹ Nov 30 '12 at 02:52
1 Answers
15
There's no fundamental problem with doing this, since tables and views are both relation variables in the relational model. Foreign key constraints are just a particular subclass of general constraints, which standard SQL implements using CREATE ASSERTION so there's no particular SQL language difficulty either.
According to this Connect item (no longer available, and not archived) it's simply a matter of prioritization. If you would find this feature useful, vote for the item and particularly add details of your use case in the comments.
Paul White
- 83,961
- 28
- 402
- 634