我如何有一个引用另一个表的检查约束? [英] How do I have a check constraint that refers to another table?

问题描述

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

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

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

• tblItem, which has an ItemID field;

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

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

tblBadItem ，它也具有ItemID字段，并且还具有指向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.

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

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

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

• 创建一个视图 vwItem ，该视图将ItemID上tblBadItem上的tblGoodItem连接起来.

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

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

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

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

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

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

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

推荐答案

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

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

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

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. 从tblGoodItem中删除行
2. 在tblItem中更新行的ItemType
3. 在tblBadItem中插入行

这篇关于我如何有一个引用另一个表的检查约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！