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.

Answer 1

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