我如何有一个引用另一个表的检查约束？

Question

我在SQL Server 2008数据库中具有以下表：

• tblItem ，它具有一个 ItemID 字段；

• tblGoodItem ，它也有一个ItemID字段，并且有一个指向tblItem的外键；

• tblBadItem ，它也具有ItemID字段，并且还具有指向tblItem的外键。

一个项目不能同时是好项目和坏项目。它必须是一个或另一个。但是，无论该项目是好是坏，它都必须是一个项目。

我的问题是： 如何在tblGoodItem和tblBadItem的ItemID字段中添加约束，以便两个表中都不存在ItemID值 ？

我已经阅读了Stack Overflow关于类似问题的一些答复，并且正在考虑以下解决方案：

• 创建一个视图 vwItem ，该视图在ItemID上的tblBadItem上连接tblGoodItem。

• 编写一个UDF fnItem ，它对vwItem进行查询，以查看视图中存在多少记录。

• 具有一个约束，该约束调用fnItem并验证返回的值为0。

这是最好的主意吗？有谁有更好的主意吗？

Answer 1

添加一列tblItem.ItemType列。此列在任何给定的行上只能有一个值（显然）。在ItemID，ItemType上添加唯一约束。

现在的诀窍是：很少有人记住这一点，但是外键可以引用唯一约束的列。

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) 
);

如果将每个子表中的ItemType约束为固定值，则tblItem中的给定行只能由一个子表引用。

但是，将项目从好更改为坏的过程分为三个步骤：

1. 从tblGoodItem删除行
2. 在tblItem中更新行的ItemType
3. 在tblBadItem中插入行