October 20, 2011 at 7:41 am
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;
October 20, 2011 at 4:06 pm
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
October 21, 2011 at 2:17 am
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.
October 31, 2011 at 10:26 am
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
January 12, 2012 at 7:32 am
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.January 12, 2012 at 4:11 pm
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));
March 9, 2012 at 2:51 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply