How do I have a check constraint that refers to another table?

Question

I have the following tables in a SQL Server 2008 db:

• tblItem, which has an ItemID field;

• tblGoodItem, which also has an ItemID field, and has a foreign key pointing to tblItem;

• tblBadItem, which also has an ItemID field, and also has a foreign key pointing to tblItem.

An item cannot be both a good item and a bad item; it must either be the one or the other. However, whether the item is good or bad, it must be an item.

My question is this: how do I add a constraint to the ItemID fields in both tblGoodItem and tblBadItem so that an ItemID value cannot exist in both tables?

I've read some replies in Stack Overflow on similar questions, and I'm thinking of this solution:

• Create a view vwItem which joins tblGoodItem on tblBadItem on ItemID.

• Write a UDF fnItem which does a query on vwItem to see how many records exist in the view.

• Have a constraint which calls fnItem and verifies that the value returned is 0.

Is this best idea? Does anyone have a better idea?

Answer 1

Add a column tblItem.ItemType column. This column can have only one value on any given row (obviously). Add a unique constraint over ItemID,ItemType.

Now the trick: few people remember this, but a foreign key can reference the columns of a unique constraint.

CREATE TABLE tblItem (
  ItemID INT PRIMARY KEY,
  ItemType CHAR(1),
  UNIQUE KEY (ItemID, ItemType)
);

CREATE TABLE tblGoodItem (
  ItemID INT PRIMARY KEY,
  ItemType CHAR(1),
  CHECK (ItemType='G')
  FOREIGN KEY (ItemID, ItemType) REFERENCES tblItem(ItemID, ItemType) 
);

CREATE TABLE tblBadItem (
  ItemID INT PRIMARY KEY
  ItemType CHAR(1),
  CHECK (ItemType='B')
  FOREIGN KEY (ItemID, ItemType) REFERENCES tblItem(ItemID, ItemType) 
);

If you constrain ItemType in each of the child tables to a fixed value, then a given row in tblItem can be referenced by only one child table.

It's a three-step process to change an item from good to bad, though:

1. DELETE row from tblGoodItem
2. UPDATE row's ItemType in tblItem
3. INSERT row in tblBadItem