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));