sql-server - SQL Server 检查约束在正确输入时失败

Question

我在表上使用检查约束来限制在表中插入的值。

这是对我正在尝试做的事情的解释

如果任何产品(轿车)与特定的 ObjLevel(丰田)相关联，则同一产品不能与另一个特定的 ObjLevel(雷克萨斯)相关联

在我对表应用检查约束后，任何包含 ObjLevel“toyota”或“lexus”的插入都会失败。

create table ObjLevel(
OLID int identity,
Name varchar(50) not null
)

insert into ObjLevel values('Ford')
insert into ObjLevel values('Toyota')
insert into ObjLevel values('Lexus')
insert into ObjLevel values('GM')
insert into ObjLevel values('Infiniti')


create table ObjInstance(
OLIID int identity (20,1),
OLID int
)

insert into ObjInstance values(1)
insert into ObjInstance values(2)
insert into ObjInstance values(3)
insert into ObjInstance values(4)
insert into ObjInstance values(5)


create table Product(
PID int identity(50,1),
Name varchar(20)
)

insert into Product values ('sedan')
insert into Product values ('coupe')
insert into Product values ('hatchback')


create table ObjInstanceProd(
OLIID int,
PID int
)


create FUNCTION [dbo].[fnObjProd] (@Pid int) RETURNS bit WITH EXECUTE AS CALLER AS

BEGIN
   DECLARE @rv bit
   DECLARE @cnt int
   SET @cnt = 0
   SET @rv = 0

      SET @cnt= 
            (Select Count(*) from ObjInstanceProd olip
            join ObjInstance oli 
            on olip.OLIID = oli.OLIID
            join ObjLevel ol
            on ol.OLID = oli.OLID
            where ol.Name in ('Toyota','Lexus')
            and PID = @Pid)

            if(@cnt>0)
                SET @rv = 1

   RETURN @rv
END


ALTER TABLE [dbo].[ObjInstanceProd]  WITH CHECK ADD  CONSTRAINT [CK_OLIP] CHECK  ([dbo].[fnObjProd]([PID])=0)


--Insert Statement    
insert into ObjInstanceProd(OLIID,PID) values (22,51)

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_OLIP". The conflict occurred in database "tmp", table "dbo.ObjInstanceProd", column 'PID'.
The statement has been terminated.


--Execute Function    
select [dbo].[fnObjProd] (51) 
0

最初 Table ObjInstanceProd 是空的。所以，无论我在表中输入什么值，只要约束中的函数返回 0，它就应该接受它。但它不接受。该函数正确返回 0(独立执行时)，但由于某种原因，检查约束返回 1

Answer 1

当 CHECK 约束触发时，该行已经在表中。因此，该函数被调用，并且由于查询返回了一行，该函数返回 1，而不是 0。试试这个。删除约束，成功插入行，然后运行此查询:

SELECT OLIID, PID, dbo.fnObjProd([PID]) FROM dbo.ObjInstanceProd;

它应该为 PID 的每个值返回 1。现在尝试添加约束。它会因为同样的原因而失败。

您是否考虑过为此使用触发器？如果您使用检查约束，这会将任何多行插入或更新变成幕后的游标。根据您接触表的方式，这绝对会降低性能和并发性。这是一个简单的 INSTEAD OF INSERT 触发器，用于防止单个操作中出现错误值，即使对于多行插入也是如此:

CREATE TRIGGER dbo.trObjProd 
ON dbo.ObjInstanceProd
INSTEAD OF INSERT AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS
    (
      SELECT 1 FROM inserted
      WHERE EXISTS 
      (
        SELECT 1
            FROM dbo.ObjInstanceProd AS olip
            INNER JOIN dbo.ObjInstance AS oli 
            ON olip.OLIID = oli.OLIID
            INNER JOIN dbo.ObjLevel AS ol
            ON ol.OLID = oli.OLID
        WHERE 
            ol.Name in ('Toyota','Lexus')
            AND olip.PID = inserted.PID
      )
  )
  BEGIN
    INSERT ObjInstanceProd(OLIID, PID)
        SELECT OLIID, PID FROM inserted;
  END
  ELSE
  BEGIN
    RAISERROR('At least one value was not good.', 11, 1); 
    SELECT OLIID, PID FROM inserted;
  END
END
GO

如果您要坚持使用一个函数，这是一种更有效的方法，但是您需要定义一种方法来确定正在插入的当前行被排除在检查之外 - 我无法确定如何这样做是因为 dbo.ObjInstanceProd 没有任何限制。 OLIID、PID是否唯一？

ALTER FUNCTION [dbo].[fnObjProd]
(
    @Pid INT
) 
RETURNS BIT
WITH EXECUTE AS CALLER 
AS
BEGIN
    RETURN 
    (
        SELECT CASE WHEN EXISTS 
        (
            SELECT 1 
                FROM dbo.ObjInstanceProd AS olip
                INNER JOIN dbo.ObjInstance AS oli 
                ON olip.OLIID = oli.OLIID
                INNER JOIN dbo.ObjLevel AS ol
                ON ol.OLID = oli.OLID
            WHERE 
                ol.Name in ('Toyota','Lexus')
                AND olip.PID = @Pid
        ) THEN 1 ELSE 0 END
    );
END
GO