CODEWITHSUNDEEP
sql-serverstored-procedurescheck-constraints
Dave Mateer
Dave Mateer

Reputation: 17946

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

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:

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

Upvotes: 3

Views: 2397

Answers (1)

A-K
A-K

Reputation: 17080

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.

Upvotes: 5

Related Questions