Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2012

    Unanswered: 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)
    returns bit

    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


    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?


    Last edited by mmallkc; 10-30-12 at 12:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts