Home Forums SQL Server 2008 SQL Server 2008 - General Check Constraint Works on Inserts, but Not on Updates RE: Check Constraint Works on Inserts, but Not on Updates

  • November 2, 2012 at 5:20 pm

    #1555701

    mmallkc (10/30/2012)

    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?

    Mike

    Without testing it yet, I agree with GSquared that the CHECK occurs before the row is actually UPDATEd. In fact, I'm not sure why then the INSERT CHECK is catching the dup (is it possibly something else causing the INSERT to fail??).

    I'm taking it that NPIID is not a unique id itself right? If it were, the check would always pass, right?

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.