Make SQL Server use CHECK CONSTRAINT only on inserted or updated rows

Question

I would like to apply a business rule to the rows in a table going forward (SQL Server 2008). However, there is historical data in the table that will not conform to this rule, and there is no good way to make it conform (unknown values with no reasonable defaults.) So I would like the CHECK CONSTRAINT to only be checked on newly inserted rows, or when a row is updated.

I created such a constraint, and set the following values:

• Check Existing Data On Creation Or Re-Enabling: No
• Enforce For INSERTs And UPDATEs: Yes
• Enforce For Replication: Yes

Everything seems fine when I insert a new record. However, if I update a record, the CHECK CONSTRAINT fails even though the updated record conforms to the CHECK CONSTRAINT. It is as if it were trying to apply the constraint to all the rows when a single row is being updated. How can I prevent this?

Here is the constraint:

([DateGiven] IS NULL 
 AND [PrimaryConsultantId] IS NULL 
 AND [AdditionalConsultants] IS NULL 
 AND [ResultingClaLevel] IS NULL)
OR ([DateGiven] IS NOT NULL 
 AND [PrimaryConsultantId] IS NOT NULL 
 AND [AdditionalConsultants] IS NOT NULL 
 AND [ResultingClaLevel] IS NOT NULL))

The updates are being done through a stored proc: (ClaEvaluationId is the primary key)

CREATE PROCEDURE [dbo].[UpdateProc] (
    @ClaEvaluationId int,
    @DateScheduled datetime,
    @DateGiven datetime,
    @PrimaryConsultantId int,
    @AdditionalConsultants nvarchar(500),
    @ResultingClaLevel decimal(2,1)
) AS

SET NOCOUNT ON

UPDATE [dbo].[ClaEvaluation]
SET
    [DateScheduled] = @DateScheduled
    ,[DateGiven] = @DateGiven
    ,[PrimaryConsultantId] = PrimaryConsultantId
    ,[AdditionalConsultants] = @AdditionalConsultants
    ,[ResultingClaLevel] = @ResultingClaLevel
WHERE [ClaEvaluationId] = @ClaEvaluationId

Answer 1

The following line in your procedure is wrong:

,[PrimaryConsultantId] = PrimaryConsultantId

should be

,[PrimaryConsultantId] = @PrimaryConsultantId

Your constraint is working as expected, and exposing a bug for you.