sql - 如何拥有引用另一个表的检查约束？

Question

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

• tblItem，其中有一个 ItemID 字段；

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

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

一个元素不可能既是好元素又是坏元素；它必须是其中之一或另一个。不过，不管这个元素是好是坏，它一定是一个元素。

我的问题是这样的:如何向 tblGoodItem 和 tblBadItem 中的 ItemID 字段添加约束，以便 ItemID 值不能同时存在于两个表中？

我在 Stack Overflow 上阅读了一些关于类似问题的回复，我正在考虑这个解决方案:

• 创建一个 View vwItem，它将 tblGoodItem 加入到 ItemID 上的 tblBadItem 上。

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

• 有一个调用 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 中插入行