Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers 2

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.

share|improve this answer
    
I apologize, I assumed it would violate the constraint –  shannon Dec 5 '13 at 4:48
    
yes, i think you also need owneditem.ownerid to allow nulls –  Nick Kavadias Dec 5 '13 at 4:50
    
that would violate my rules, though. if the item doesn't have an owner it won't be useful to the owner –  shannon Dec 5 '13 at 4:52
    
I am trying out your solution right now –  shannon Dec 5 '13 at 4:52
    
Yeah, as I suspected, null do not match the constraint as defined above. There are no primary or candidate keys in the referenced table 'Category' that match the referencing column list in the foreign key –  shannon Dec 5 '13 at 5:02
show 6 more comments
up vote 0 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.