Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filtered Foreign Key Expand / Collapse
Should Microsoft implement a filtered foreign key?
Poll ResultsVotes
Definitely
 
37.5%
9
Could be nice
 
41.67%
10
I don't care
 
4.17%
1
I don't see the value of it
 
4.17%
1
Definitely not
 
12.5%
3
Member Votes: 20, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Thursday, October 20, 2011 7:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:15 AM
Points: 827, Visits: 1,361
A conceptual thought: We have now (from SQL Server 2008) got filtered indexes. What do you think of the idea of having filtered foreign keys as well. In the example below we can think of ReferenceId being a SID, Guid or other value that is unique across both users and groups.

alter table AuditLog
add constraint fk_AuditLog_Users foreign key (ReferenceId) references Users (UserId)
where IsUser = 1;
alter table AuditLog
add constraint fk_AuditLog_Groups foreign key (ReferenceId) references Groups (GroupId)
where IsGroup = 1;





Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL
Post #1193688
Posted Thursday, October 20, 2011 4:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 7,094, Visits: 12,579
Interesting. Your syntax is clean and intuitive. Playing devil's advocate, in thinking about sys.database_principals where groups and users exist together and both share the same set of surrogate keys, I am wondering if the filtered FK feature would be compensating for a flawed data model. A feature like this also seems like it would affirm the violation of the idea that a column should represent only one type of entity. Have you looked at ANSI-SQL standards to see if a similar feature existed?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1194063
Posted Friday, October 21, 2011 2:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:15 AM
Points: 827, Visits: 1,361
Thanks for the repsonse. As far as I've seen, there is no such thing in the SQL Standards. I surely see your points, and in fact it is kind of the intention too allow a single column represent several types of entities. It would be a nice feature for generalization. We may have the following scenario: We have 20+ tables for which changes should be audited. Referential integrity should be enforced. How can we solve this today?

1. A dedicated audit table for each table which should be audited.
2. A singe audit table, and a link table with one (sparse) column for each table which should be audited, and a check constraint enforcing only one of these being filled out.
3. A single audit table, and triggers to enforce referential integrity.

In my opinion, even though it is not in the SQL Standard, a kind of filtered foreign key would be a cleaner solution than any of these.

We could also think of the following: A system with the concept of open/closed, active/inactive or something similar. Open orders have all their data in a set of table, but closed orders have some data moved to different tables for perforamnce reasons since they're seldom queried.

So, all of this can be solved in other ways, but personally I would have liked to have a filtered foreign key.

Finally... As for standards, everything in the standard has as some time not been in the standard, either because the standard did not exist or because it was a new and (hopefully) bright idea.




Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL
Post #1194176
Posted Monday, October 31, 2011 10:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 7,094, Visits: 12,579
okbangas (10/21/2011)
...and in fact it is kind of the intention too allow a single column represent several types of entities. It would be a nice feature for generalization.

I thought about what you said, tried to apply it to other use cases, and after a while I did not like where the idea was headed. What you are proposing would be a move towards legitimizing not only audit tables but also centralized lookup tables, by adding DRI support to that concept. However still leaving the data type issue it then creates in the "value" column unaddressed...unless you consider using SQL_VARIANT, XML or NVARCHAR(MAX) as the data-type for your "value" column a proper solution. Is that a good thing? It could be useful in some scenarios I suppose, but then again it appears that it would create more problems than it would solve. Additionally, the platform already allows for us to paint ourselves into a corner this way using triggers to maintain RI manually. Your syntax would certainly be cleaner than implementing a set of triggers, however the bar is high and I think that is a good thing.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1198201
Posted Thursday, January 12, 2012 7:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
For starters, I think that this is a very interesting idea and I'm looking forward to what other people has to say about it.

On the other hand I do think that filtered indexes are one thing and a filtered FK is a totally different animal because it touches the core of referential integrity.

It would help if you can figure out how to make room on RI basics for something like this - in the mean time (you can call me conservative) I would stay with the old fashioned FK concept.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1234810
Posted Thursday, January 12, 2012 4:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 441, Visits: 3,286
okbangas (10/20/2011)
A conceptual thought: We have now (from SQL Server 2008) got filtered indexes. What do you think of the idea of having filtered foreign keys as well. In the example below we can think of ReferenceId being a SID, Guid or other value that is unique across both users and groups.


ISO Standard SQL supports a generalised form of constraint called an assertion. In fact SQL has had this feature since 1992! It's a sad fact that few SQL DBMS vendors have actually implemented it.

CREATE ASSERTION fk_AuditLog_Users
CHECK (NOT EXISTS
(SELECT ReferenceId
FROM AuditLog
WHERE IsUser = 1
EXCEPT
SELECT UserId
FROM Users));



David
Post #1235298
Posted Friday, March 9, 2012 2:51 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:41 AM
Points: 124, Visits: 260
Yes, This would be immensely helpful for the particular type of business model I am working with currently. A filtered foreign Key constraint or an ability to run a CHECK constraint against a TABLE valued function. Ex. CHECK(column_name IN(udf_somefunction()). Vote +1
Post #1264636
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse