CODEWITHSUNDEEP
sqlsql-serveruser-defined-functionscheck-constraints
noontz
noontz

Reputation: 2007

Check constraint with function fails on update although function returns correctly

I am trying to implement a CHECK constraint on table CheckTable that ensures only one row for a given number of rows with same col2 value can have the ok bit set to 1.

This is my go at the problem, and I am struggling to understand how to use the function in the check constraint as the second update passes the check although the function seems work correctly

CREATE TABLE [dbo].[CheckTable] (col1 int, col2 int, ok bit)
GO

CREATE FUNCTION [dbo].[CheckIfOk] 
(
    @col2 int
)
RETURNS bit
AS
BEGIN
    DECLARE @OK bit;
    IF(EXISTS(SELECT * FROM [dbo].[CheckTable] WHERE col2 = @col2 AND ok = 1))
    SET @OK = 1
    ELSE
    SET @OK = 0
    RETURN @OK
END
GO

ALTER TABLE [dbo].[CheckTable]
ADD CONSTRAINT chk_col2_ok CHECK (dbo.CheckIfOk(col2) = 0)
GO

INSERT INTO [dbo].[CheckTable] (col1, col2, ok) VALUES (1, 1, 0),(2, 1, 0)
GO

SELECT [dbo].[CheckIfOk](1)
GO

UPDATE [dbo].[CheckTable] SET ok = 1 WHERE col1 = 1
GO

SELECT [dbo].[CheckIfOk](1)
GO

UPDATE [dbo].[CheckTable] SET ok = 1 WHERE col1 = 2
GO

It's probably obvious, but I can't seem to nail it.

Upvotes: 1

Views: 336

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

The overall design is flawed, and it should be replaced with filtered UNIQUE index.

"This is my go at the problem, and I am struggling to understand how to use the function in the check constraint as the second update passes the check although the function seems work correctly"

The check constraint was not "fired".

-- original
UPDATE [dbo].[CheckTable] SET ok = 1 WHERE col1 = 1;

-- enforcing col2 update: 1:1 -> no real change
UPDATE [dbo].[CheckTable] SET ok = 1, col2=col2 WHERE col1 = 1;

Result:

Msg 547 Level 16 State 0 Line 1
The UPDATE statement conflicted with the CHECK constraint "chk_col2_ok".

db<>fiddle demo

Related: MSSQL: Update statement avoiding the CHECK constraint

Upvotes: 2

SMor
SMor

Reputation: 2872

Just don't - scalar functions are notorious efficiency problems. Your goal can be better (IMO) accomplished with a much simpler approach - a unique filtered index. 

if object_id('dbo.CheckTable') is not null 
   drop table dbo.CheckTable;
go
CREATE TABLE [dbo].[CheckTable] (col1 int, col2 int, ok bit)
GO

create unique nonclustered index ixx on dbo.CheckTable(col2) where ok = 1;
go

-- all valid
insert dbo.CheckTable(col1, col2, ok) 
values (1, 1, 0), (2, 2, 1), (3, 0, 0);
go
-- still valid
insert dbo.CheckTable(col1, col2, ok) 
values (4, 1, 1);
go
-- not valid
insert dbo.CheckTable(col1, col2, ok) 
values (5, 1, 1);
go
-- not valid, dup in inserted batch
insert dbo.CheckTable(col1, col2, ok) 
values (6, 8, 1), (7, 8, 1);
go
-- valid
insert dbo.CheckTable(col1, col2, ok) 
values (9, 1, 0);
go

select * from dbo.CheckTable order by col2, ok, col1;
go

Upvotes: 1

Related Questions