代码之家 › 专栏 › 技术社区 › Tola Odejayi

如何具有引用另一个表的检查约束?

check-constraints sql-server-2008 tsql sql-server sql

Tola Odejayi · 技术社区 · 14 年前

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

TBLATION ,它有一个项目田野;
总目还具有itemID字段,并且具有指向tblitem的外键;
TBLBAD项目 也有一个itemID字段,还有一个指向tblitem的外键。

一个项目不能同时是一个好项目和一个坏项目;它必须是一个或另一个。但是,无论项目是好的还是坏的,它都必须是项目。

我的问题是: 如何向tblgooditem和tblbaditem中的itemID字段添加约束,以便itemID值不能同时存在于这两个表中 ?

我已经阅读过类似问题的堆栈溢出中的一些回复,我正在考虑这个解决方案:

创建视图 维维姆 它连接itemID上tblbaditem上的tblgooditem。
写一个UDF 费尼姆 它对vwitem进行查询,以查看视图中有多少记录。
具有一个调用fnitem并验证返回值是否为0的约束。

这是个好主意吗?有人有更好的主意吗?

5 回复 | 直到 14 年前

Bill Karwin 14 年前

添加列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中的给定行只能由一个子表引用。

把一个物品从好的改成坏的过程分为三步,不过:

从tblgooditem中删除行
在tblitem中更新行的itemtype
在tblbaditem中插入行

KM. 14 年前

去掉tblgooditem和tblbaditem,用itemtype=“g”或“b”创建一个新表,在itemID上放置一个唯一的索引或键,那么就不需要对tblitem进行约束。

John Sansom 14 年前

A-K 14 年前

codingbadger 14 年前

SELECT CHECK

CREATE FUNCTION dbo.fn_CheckItems(@itemId INT) RETURNS BIT

AS BEGIN

DECLARE @i INT,
        @rv BIT


SET @i = 0

IF (SELECT COUNT(*) FROM tblBadItem WHERE ItemId = @ItemId) > 0
BEGIN
SET @i = 1
END


IF (SELECT COUNT(*) FROM tblGoodItem WHERE ItemId = @ItemId) > 0
BEGIN
SET @i = @i + 1
END

IF (@i > 1)
BEGIN
    SET @rv = 1
END
ELSE
BEGIN
    SET @rv =0
END


RETURN @rv

END
GO

CREATE  TABLE tblItem (
  ItemID INT IDENTITY(1,1) PRIMARY KEY,
  DateAdded DATETIME
)
GO

CREATE TABLE tblGoodItem (
  ItemID INT PRIMARY KEY,
  CHECK (dbo.fn_CheckItems(ItemId) = 0)

)
GO

CREATE TABLE tblBadItem (
  ItemID INT PRIMARY KEY,
  CHECK (dbo.fn_CheckItems(ItemId) = 0)
)
GO

INSERT INTO tblItem (DateAdded)
VALUES (GETDATE())

INSERT INTO tblGoodItem(ItemID)
SELECT ItemId FROM tblItem

--This statement will fail as the ItemId is already in GoodItems
INSERT INTO tblBadItem(ItemID)
SELECT ItemId FROM tblItem


DROP TABLE tblItem
DROP TABLE tblGoodItem
DROP TABLE tblBadItem
DROP FUNCTION dbo.fn_CheckItems