SQL Server - Constraint using UDF always violates

Asked By Zodma

23-Sep-08 01:43 AM

I'm trying to write a check constraint that uses a UDF to validate whenever
an INSERT or UPDATE occures.  I can write the UDF and configure the
constraint, however when I do an insert I always get an "INSERT statement
conflicted with the CHECK constraint" error, even though if I call the UDF
directly I get the right result.

Here's an example.  Create the table Items:

-----------------------------------------------------------
CREATE TABLE [dbo].[Items] (
[Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
-----------------------------------------------------------

Next, create the UDF:

-----------------------------------------------------------
create FUNCTION [dbo].[CheckItem]
(
@Name nvarchar(50)
)
RETURNS bit
AS
BEGIN
DECLARE @Result bit
set @result = cast(0 as bit)
if	(	select	count([name])
from	Items
where	[name] = @name
) = 0
begin
set @Result = cast(1 as bit)
end
RETURN @Result
END
-----------------------------------------------------------

Then set up the constraint:

-----------------------------------------------------------

ALTER TABLE dbo.Items WITH NOCHECK ADD CONSTRAINT CK_Items CHECK
(dbo.CheckItem([Name])=(1))

-----------------------------------------------------------

Now try an insert and witness the error:

-----------------------------------------------------------

declare @name as nvarchar(50)
set @name='item 1'

insert into items values (@name)

-----------------------------------------------------------

However, the function returns (1):

-----------------------------------------------------------

declare @name as nvarchar(50)
set @name='item 1'

select dbo.checkitem(@name)

if dbo.checkitem(@name)=(1)
select 'Yes'
else
select 'No'
-----------------------------------------------------------

What is wrong with this constraint?

Carl.

23-Sep-08 02:02 AM

Personally, I don't use constraints that work over several rows, for the reasons you see here. If
you check the estimated execution plan, you will see that the insert is performed first, then the
check constraint is verified. This might not seem intuitive, but that is what is performed. I don't
know whether there is documented *when* the various types of constraints are done, logically, but as
I mentioned, I avoid check constraints being dependent on other rows in the same table for these
reasons. Your example could have been done with a unique constraint (methinks), but I assume it was
only an example...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

23-Sep-08 03:12 AM

Ahhh, thank you.  This was driving me crazy, as I -was- expecting the UDF to
be executed before the insert.

I'm a novice with using execution plans, but from your response I can now
see that the insert is executed first.

And yes, the example was just an example.  The real use case I'm trying to
resolve is a table that stores either unique or non-unique values of certain
fields depending on the value of a foreign key.

Now that I can see how the UDF constraint works I may normalise the design
instead anyway for performance reasons.

Thanks again!
Carl.