SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filtered Foreign Key


Filtered Foreign Key

Poll
Should Microsoft implement a filtered foreign key?

37.93% - 11 votes Definitely
37.93% 11 votes
44.83% - 13 votes Could be nice
44.83% 13 votes
3.45% - 1 vote I don't care
3.45% 1 vote
3.45% - 1 vote I don't see the value of it
3.45% 1 vote
10.34% - 3 votes Definitely not
10.34% 3 votes
Member votes: 25, Guest votes: 0. You don't have permission to vote in this poll
Author
Message
okbangas
okbangas
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2615 Visits: 1387
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39138 Visits: 14411
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
okbangas
okbangas
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2615 Visits: 1387
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39138 Visits: 14411
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14027 Visits: 4639
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.
sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2080 Visits: 3706
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));
GP Van Eron
GP Van Eron
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3560 Visits: 472
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search