Check Constraint Works on Inserts, but Not on Updates

mmallkc

New Member
I created a UDF to check for more than one instances of a value 1 in a bit column corresponding to a given ID in a table.

CREATE function [dbo].[udfDefault4Scheduling] (@intNPIID int)
returns bit
as
begin

declare @bit bit

set @bit = (select case when count(NPIID) <= 1 then 1 else 0 end from NPIOrgAddressAvailability where NPIID = @intNPIID and Default4Scheduling = 1)

return @bit

end

I added a check constraint on the table in question (NPIOrgAddressAvailability) requiring the function to return a value of 1 for a given ID. In other words, the table can have multiple repeated values for NPIID; but, for a given NPIID, only one row can have a value of 1 for Default4Scheduling.

ALTER TABLE [dbo].[NPIOrgAddressAvailability] WITH CHECK ADD CONSTRAINT [CK_NPIOrgAddressAvailability] CHECK (([dbo].[udfDefault4Scheduling]([NPIID])=(1)))
GO

ALTER TABLE [dbo].[NPIOrgAddressAvailability] CHECK CONSTRAINT [CK_NPIOrgAddressAvailability]
GO

Whenever I try to insert a new record in violation of this constraint, I get an error as expected. However, when I update the table in violation of this constraint, it allows me to do so as if the constraint doesn't even exist.

I checked in SSMS and "Enforce for Inserts and Updates" is set to "Yes".

A Google search suggests several others have had this issue; but, I haven't seen a resolution. Any ideas?

Thanks,

Mike
 

Peter Schmitz

Administrator
Staff member
Hi Mike, and welcome to MSSQLForum.

What is the data type of the NPIID column? In case it is not a bit, try and see if changing the datatype returned by the udf to the same data type as the column does anything?

I.e. if the data type of NPIID is int, make the variable in the UDF an int.

According to this article, there might be issues when a constraint needs to perform a data type conversion. If that won't do the trick, it might be better to rework the logic into a TRIGGER instead. There do seem to be some issues with constraints calling UDFs.
 

mmallkc

New Member
Thanks Peter,

The resolution was to use a filtered index rather than a constraint. Still not sure why the constraint didn't work on updates. Perhaps the UDF was being executed before the update was committed; so, no error resulted.

Thanks anyway.
 
Top