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