Why doesn't this Check constraint work?

Question

I have created a check constraint in SQL Server 2005, but this check constraint doesn't work. The SQL Server Management Studio tells me by an insert statement the following message:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "MY_CHECK_CONSTAINT". The conflict occurred in database "MY_DB", table "dbo.MY_TABLE", column 'MY_COLUMN'.

I have added the check constraint with the following code:

ALTER TABLE MY_TABLE WITH NOCHECK
ADD CONSTRAINT CK_MY_CHECK_CONSTRAINT CHECK (dbo.MY_FUNCTION(MY_PARAMETER)<1)

The calling function "MY_FUNCTION" returns an int.

My target is that if my function returns an int which is less than 1 the insert statement can successfully be completed and if the return value is bigger than 0 the insert statement has to be terminated.

My problem now is that my function returns the value 0 but the insert statement has been terminated always. What am I doing wrong?

The code of my function is the following:

CREATE FUNCTION MY_FUNCTION(@MY_PARAMETER uniqueidentifier) 
RETURNS int 
AS 
BEGIN 
    declare @return int = 0
    SET @return = (SELECT COUNT(MY_COLUMN) FROM MY_TABLE WHERE MY_COLUMN= @MY_PARAMETER )
return @return 
END

Thanks for your help.

What's wrong is that your *assumption* that the function is returning 0 is incorrect. But we can't tell you why it's incorrect since you haven't shown us the code for it. — Damien_The_Unbeliever, Nov 26 '13 at 09:26
I have god added now the code of my function. I hope that helps you. — nikola.stankovic, Nov 26 '13 at 09:32
Just add a unique constraint on `MY_COLUMN`. Don't try and use a check constraint with a scalar UDF for this. — Martin Smith, Nov 26 '13 at 15:13
Hi Martin, I can't use an unique constraint because MY_COLUMN can be NULL. — nikola.stankovic, Nov 28 '13 at 08:28

score 2 · Accepted Answer · edited May 23 '17 at 11:49

I was able to reproduce your problem in 100%. Try this new
example below. Now my table table1 is empty, and I cannot insert
any number into it :) Very interesting situation, absolutely the
same as yours, I believe.

"Maybe when your UDF code is executed, it already sees this same
row which you're just trying to insert (it sees it's in the table).
I don't know the inner workings and don't have much time now to check it.
But that could be the issue. My UDF doesn't perform a check based on some
SELECT in the same table, that's what's conceptually different between
your example and my example."

OK, after 5 more minutes of research, turns out my guess was right.
When your UDF is called, it sees the row you're just trying
to insert.

See the accepted answer here.

Check constraint UDF with multiple input parameters not working

So - mystery uncovered, it seems :)

--- 1 ---

USE [test]
GO

/****** Object:  UserDefinedFunction [dbo].[ContainsNumber]    Script Date: 11/26/2013 07:06:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ContainsNumber]
(
        @number int
)
RETURNS INT AS

BEGIN

declare @result int

select @result = count(*)
from test.dbo.table1
where
number = @number

if (@result > 0) 
begin
    set @result = 1
end

return @result

END


GO



--- 2 ---

USE [test]
GO

/****** Object:  Table [dbo].[table1]    Script Date: 11/26/2013 07:06:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[table1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [number] [int] NULL,
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [CK_table1] CHECK  (([dbo].[ContainsNumber]([number])=(0)))
GO

ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [CK_table1]
GO

Thanks you very much for your help :) now everything works fine. That was the solution. — nikola.stankovic, Nov 27 '13 at 07:13

peter.petrov · Answer 2 · 2013-11-26T09:40:51.053

0

1) Maybe you can try this in your code:

SELECT @return = COUNT(MY_COLUMN) 
FROM MY_TABLE WHERE MY_COLUMN = @MY_PARAMETER

instead of what you did.

2) Try naming the variable not @return but e.g. @result.

3) Try walking your function outside of its normal call.

4) Also, you might want to try this test below. I don't see any issues with it ... But OK, it's not 100% the same as yours.

This check constraint below works fine and uses my user defined function IsEvenNumber.

----- 1 -----

USE [test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE FUNCTION [dbo].[IsEvenNumber]
(
        @number int
)
RETURNS INT AS

BEGIN

declare @result int

set @result = 1

if (((@number) % 2) <> 0)
begin
    set @result = 0
end


return @result


END



GO




----- 2 -----


USE [test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[table1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [number] [int] NULL,
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [CK_Table1] CHECK  (([dbo].[IsEvenNumber]([number])<(1)))
GO

ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [CK_Table1]
GO



----- 3 -----


insert into table1(number) values (3) -- OK

insert into table1(number) values (10) -- FAILED

insert into table1(number) values (0) -- FAILED

insert into table1(number) values (5) -- OK

edited Nov 26 '13 at 09:40

answered Nov 26 '13 at 09:34

peter.petrov

38,363
16
94
159

If I do your solution everything works fine but when I try to do the same with my function and my table so i get the following error message: Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the CHECK constraint "CK_MY_CONSTRAINT". The conflict occurred in database "MY_DB", table "dbo.MY_TABLE", column 'MY_COLUMN'. – nikola.stankovic Nov 26 '13 at 09:46
I don't know why but I can't alter my table to add the check constraint without placing the NOCHECK. – nikola.stankovic Nov 26 '13 at 09:51
I tried to call my function outside with a fixed value as a parameter and I'm sure that my function works as I want. – nikola.stankovic Nov 26 '13 at 09:54
Maybe when your UDF code is executed, it already sees this same row which you're just trying to insert (it sees it's in the table). I don't know the inner workings and don't have much time now to check it. But that could be the issue. My UDF doesn't perform a check based on some SELECT in the same table, that's what's conceptually different between yours and mine. – peter.petrov Nov 26 '13 at 09:54
Maybe that's the problem but I wonder why I can't alter my table and add the check constraint without the extension of NOCHECK – nikola.stankovic Nov 26 '13 at 10:01
Not sure if I understand this correctly but maybe because the existing data in tour table already violates your constraint. – peter.petrov Nov 26 '13 at 11:59

Why doesn't this Check constraint work?

2 Answers2