October 30th, 2012, 12:54 PM
Check Constraint Works on Inserts, but Not on Updates
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)
declare @bit bit
set @bit = (select case when count(NPIID) <= 1 then 1 else 0 end from NPIOrgAddressAvailability where NPIID = @intNPIID and Default4Scheduling = 1)
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)))
ALTER TABLE [dbo].[NPIOrgAddressAvailability] CHECK CONSTRAINT [CK_NPIOrgAddressAvailability]
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?