SQL Server 2012 CHECK constraint not firing on UPDATE or DELETE statements

Question

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.

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

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)

As expected, this INSERT statement fails:

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

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?

Notable

See MSSQL: Update statement avoiding the CHECK constraint – Businesslike

Please stop combing check constraints and UDFs. Several articles here explain how wrong this can go. – Gilligan

Answer 1

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)

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

Usually the constraint will look something like

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

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)

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

Mustee

looks like my scenario, you have to pass extra parameter even if function doesn't use it – Crayton