SQL Server 2012 CHECK约束未在UPDATE或DELETE语句上触发 [英] SQL Server 2012 CHECK constraint not firing on UPDATE or DELETE statements

问题描述

我正在尝试对表执行数据验证，并且需要检查多个行和列.基本上，我想确保对于任何给定的工具，至少存在一个被标记为活动版本或Beta版本的版本.

I'm trying to perform data validation on a table, and I need to check across multiple rows and columns. Basically I want to make sure that for any given tool, at least one version exists that is flagged as either the Active version, or the Beta version.

我想检查的唯一方法是通过用户定义的标量函数和调用该函数的CHECK约束.我能够创建返回期望值的函数(如果可以，则返回0，否则返回1)，并且可以正常工作……但是在INSERT上仅 .当我执行UPDATE或DELETE语句时，检查约束不会失败，因此无论哪种情况，我最终都会得到错误的数据.

The only way I could think to check this is through a user-defined scalar function, and a CHECK constraint that calls the function. I was able to create the function that returns what I expect (0 if OK, 1 if not) and it works... but only on an INSERT. When I perform an UPDATE or DELETE statement, the check constraint doesn't fail, so I can end up with bad data in either case.

这是我正在使用的表和函数的示例.

Here's a sample of the table and function I'm using.

CREATE TABLE VersionTest(VersionID int NOT NULL, ToolID int NOT NULL, IsActiveVersion bit NOT NULL, IsBetaVersion bit NOT NULL)
GO

ALTER TABLE VersionTest ADD  CONSTRAINT [DF_VersionTest_IsActiveVersion]  DEFAULT ((0)) FOR [IsActiveVersion]
GO

ALTER TABLE VersionTest ADD  CONSTRAINT [DF_VersionTest_IsBetaVersion]  DEFAULT ((0)) FOR [IsBetaVersion]
GO

CREATE FUNCTION fn_ValidateVersionTest(@toolID int) RETURNS SMALLINT
AS
      BEGIN
      IF (@toolID = -1)
        BEGIN
          RETURN 0
        END
      ELSE
        BEGIN
            IF EXISTS (SELECT 1
                       FROM VersionTest
                       WHERE ToolID = @toolID
                       GROUP BY ToolID, IsActiveVersion, IsBetaVersion
                       HAVING (SUM(CASE WHEN IsActiveVersion = 1 THEN 1 ELSE 0 END) + 
                               SUM(CASE WHEN IsBetaVersion = 1 THEN 1 ELSE 0 END)) > 0)
              RETURN 0
        END
        RETURN 1      
    END
GO

ALTER TABLE VersionTest WITH CHECK ADD  CONSTRAINT [CK_VersionTest] CHECK  (([dbo].[fn_ValidateVersionTest]([ToolID])=(0)))
GO
ALTER TABLE VersionTest CHECK CONSTRAINT [CK_VersionTest]
GO

以下INSERT语句可以正常工作.

The following INSERT statements work just fine.

INSERT INTO VersionTest (ToolID, VersionID, IsActiveVersion, IsBetaVersion)
VALUES (1, 1, 1, 0)

INSERT INTO VersionTest (ToolID, VersionID, IsActiveVersion, IsBetaVersion)
VALUES (1, 2, 0, 0)

按预期，此INSERT语句失败:

As expected, this INSERT statement fails:

INSERT INTO VersionTest (ToolID, VersionID, IsActiveVersion, IsBetaVersion)
VALUES (2, 1, 0, 0)

但是，当我期望以下UPDATE和DELETE语句不会失败时:

However, the following UPDATE and DELETE statements aren't failing when I expected them to:

UPDATE VersionTest 
SET IsActiveVersion = 0, IsBetaVersion = 0
WHERE VersionID = 1

DELETE VersionTest WHERE VersionID = 1

我还有另一种方法应该这样做吗?我必须使用触发器吗?

Is there another way I should be doing this? Do I have to use triggers instead?

推荐答案

我们可以通过这种方式强制进行更新检查.
假设您有一个这样的表

We can force the check on update on this way.
Lets suppose you have a table like this

create table UserTest(Id int, IsEnabled bit, [GroupId] int)

现在，我们只想检查[GroupId]是否启用了1个用户.

Now, we want to check only 1 user is enable by [GroupId].

通常约束看起来像

ALTER TABLE [dbo].[UserTest] ADD CONSTRAINT CHK_OnlyOneEnabled  CHECK (dbo.checkOnlyOne(GroupId)=1)

在更新GroupId之前，不会在记录已更新时触发此约束.
因此，我们必须通过执行此强制强制约束来验证IsEnabled列

This constraint will not be triggered over the record been updated until you update the GroupId.
So, we have to force the constraint to validate the IsEnabled column by doing

ALTER TABLE [dbo].[UserTest] ADD CONSTRAINT CHK_OnlyOneEnabled  CHECK (dbo.checkOnlyOne(GroupId, IsEnabled )=1)

是否使用IsEnabled值无关紧要.

No matters if you use or not the IsEnabled value into the function.

这篇关于SQL Server 2012 CHECK约束未在UPDATE或DELETE语句上触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！