EDIT: I've come up with one poor solution (below) that requires some denormalization, but does avoid the risks of UDFs in
CHECK constraints. I would appreciate your help if you have a better answer.
- Each Category, except any 'built-in' Category, is only visible to its respective Owner.
- Each item ("OwnedItem") must be assigned a Category that is visible to the item's Owner.
The challenge: since Owners can only see their own Categories and 'built-in' Categories, the data is invalid (for example) if one Owner's items are assigned a Category from another Owner, unless that Category is owned by no one.
Because of this, I would like to accept a null value (or a fixed value, e.g. -1) on one column of the parent in a multi-column relationship.
-- Categories have owners, or may be 'globally shared' CREATE TABLE Category ( Id int IDENTITY(1,1) NOT NULL, OwnerId int NULL, Title nvarchar(50) NOT NULL, CONSTRAINT PK_Category PRIMARY KEY (Id ASC) ) SET IDENTITY_INSERT Category ON GO INSERT INTO Category (Id, OwnerId, Title) VALUES (-1, NULL, 'Hidden'), (0, NULL, 'Default') GO SET IDENTITY_INSERT Category OFF GO -- Items have owners, -- and must be assigned a category from the same owner, -- or be assigned a global category -- i.e. prevent assignment of items to another Owner's private Category CREATE TABLE OwnedItem ( Id int IDENTITY(1,1) NOT NULL, CategoryId int NOT NULL, OwnerId int NOT NULL, Description varchar(50) NOT NULL CONSTRAINT PK_Ownedtem PRIMARY KEY (Id ASC) ) ALTER TABLE OwnedItem ADD CONSTRAINT FK_OwnedItemCategoryId FOREIGN KEY (CategoryId) REFERENCES Category (Id); ALTER TABLE OwnedItem ADD CONSTRAINT FK_CategoryOwnerOwnedItemOwner FOREIGN KEY (OwnerId) REFERENCES Category (OwnerId) -- How can I say: -- OR Category.OwnerId IS NULL ;
How can I allow
OR Category.OwnerId IS NULL immediately above?
INSERT INTO OwnedItem (CategoryId, OwnerId, Description) VALUES (-1, 23, 'Person23s hidden item') -- SHOULD SUCCEED