Accept null value on parent column in multipart relationship 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
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.