Accept null value on parent column in multipart relationship

Question

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.

1. Each Category, except any 'built-in' Category, is only visible to its respective Owner.
2. 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

Answer 1

Set [OwnerId] on OwnerID to allow nulls. Put a null value record in your Category table:

INSERT INTO Category (OwnerId,Title) SELECT NULL,'blah??'

I also added to your category table:

CONSTRAINT UC_OwnerID UNIQUE(OwnerID) 

this is what I have working for you in SQL Fiddle http://sqlfiddle.com/#!3/ac720/2.

Answer 2

I've just thought of a somewhat ugly way to enforce this. By replicating an additional copy of the key to the child table, I'm able to enforce that relationship to the parent, and then with that replicated information, perform more complex CHECK constraint logic on the child table.

1. Add a CategoryOwnerId int NULL column to Item.
2. Make the relationship on that rather than the Item.OwnerId.
3. Add a constraint to Item:

If null OwnerId is used in to represent a 'built-in' Category:

CategoryOwnerId NOT NULL OR CategoryId < 1

Or, with the same duplication of data but non-null 'built-in' Owner values:

CategoryOwnerId = OwnerId OR CategoryOwnerId = -1

Here's the change as-implemented:

DROP CONSTRAINT FK_OwnedItemCategoryId
DROP CONSTRAINT FK_CategoryOwnerOwnedItemOwner
GO

ALTER TABLE OwnedItem
ADD CategoryOwnerId INT NULL
GO

-- Prior to this schema change, all categories were system-wide/built-in
UPDATE OwnedItem
SET CategoryOwnerId = -1
GO

ALTER TABLE OwnedItem
ALTER COLUMN CategoryOwnerId INT NOT NULL
GO

ALTER TABLE OwnedItem
ADD CONSTRAINT FK_OwnedItemCategory
    FOREIGN KEY (CategoryId, OwnerId)
    REFERENCES Bin (Id, OwnerId);
GO

ALTER TABLE OwnedItem
ADD CONSTRAINT CK_OwnedItemCategoryBuiltinOrOwnerMatch
CHECK (OwnerId = CategoryOwnerId OR CategoryOwnerId = -1)
GO