Need to add a Table Level Constraint to prevent bad data - Constraint is not working

Question

I have a table that has a flag that can be set to 0 or 1. I need to make sure for every claim record in that table that the flag is only set 1 once. So in other words I may have multiple records for one claim and all but one should be set to 0. Below is the table

CREATE TABLE [dbo].[ClaimAccounting_Assignments](
[PracticeID] [int] NULL,
[ClaimID] [int] NULL,
[ClaimTransactionID] [int] NULL,
[InsurancePolicyID] [int] NULL,
[InsuranceCompanyPlanID] [int] NULL,
[PatientID] [int] NULL,
[LastAssignment] [bit] NULL,
[Status] [bit] NULL,
[PostingDate] [datetime] NOT NULL,
[EndPostingDate] [datetime] NULL,
[LastAssignmentOfEndPostingDate] [bit] NULL,
[EndClaimTransactionID] [int] NULL,
[DKPostingDateID] [int] NULL,
[DKEndPostingDateID] [int] NULL,
[RelativePrecedence] [int] NULL
) ON [PRIMARY]

And here is the constraint with the function I created to make this happen

Alter FUNCTION FN_IsLastAssigned(@ClaimId INT)
RETURNS INT
AS 

BEGIN
DECLARE @LastAssignment Int


SET @LastAssignment=(
SELECT COUNT(*)
FROM ClaimAccounting_Assignments AS caa
WHERE claimId=@ClaimID AND caa.LastAssignment=1)

RETURN @LastAssignment

END 

Here is the Constraint - It should check to see that there are no current records for that claim that have LastAssignment=1 when it inserts into the table:

ALTER TABLE ClaimAccounting_Assignments
WITH NOCHECK
ADD CONSTRAINT  CK_Constraint_CAA_LastAssignments
CHECK ( dbo.FN_IsLastAssigned(ClaimId)=0)

I have a feeling it is something silly I am missing but I don't know. Any help would be greatly appreciated

Here is some data to do testing with if you need.

INSERT INTO ClaimAccounting_Assignments

VALUES  (1
    , -- PracticeID
     1191
    , -- ClaimID
     12345
    , -- ClaimTransactionID
     0
    , -- InsurancePolicyID
     0
    , -- InsuranceCompanyPlanID
     0
    , -- PatientID
    0
    , -- LastAssignment
     NULL
    , -- Status
     '2012-07-03 16:56:49'
    , -- PostingDate
     '2012-07-03 16:56:49'
    , -- EndPostingDate
     NULL
    , -- LastAssignmentOfEndPostingDate
     0
    , -- EndClaimTransactionID
     0
    , -- DKPostingDateID
     0
    , -- DKEndPostingDateID
     0  -- RelativePrecedence
    ),
    VALUES  (1
    , -- PracticeID
     1191
    , -- ClaimID
     12346
    , -- ClaimTransactionID
     0
    , -- InsurancePolicyID
     0
    , -- InsuranceCompanyPlanID
     0
    , -- PatientID
    0
    , -- LastAssignment
     1
    , -- Status
     '2012-07-04 16:56:49'
    , -- PostingDate
     '2012-07-04 16:56:49'
    , -- EndPostingDate
     NULL
    , -- LastAssignmentOfEndPostingDate
     0
    , -- EndClaimTransactionID
     0
    , -- DKPostingDateID
     0
    , -- DKEndPostingDateID
     0  -- RelativePrecedence
    )
    ,
    VALUES  (1
    , -- PracticeID
     1191
    , -- ClaimID
     12347
    , -- ClaimTransactionID
     0
    , -- InsurancePolicyID
     0
    , -- InsuranceCompanyPlanID
     0
    , -- PatientID
    0
    , -- LastAssignment
     1
    , -- Status
     '2012-07-05 16:56:49'
    , -- PostingDate
     '2012-07-05 16:56:49'
    , -- EndPostingDate
     NULL
    , -- LastAssignmentOfEndPostingDate
     0
    , -- EndClaimTransactionID
     0
    , -- DKPostingDateID
     0
    , -- DKEndPostingDateID
     0  -- RelativePrecedence
    )

Answer 1

This line says there can be no LastAssignment turned on:

CHECK (dbo.FN_IsLastAssigned(ClaimId)=0) 

It should probably be:

CHECK (dbo.FN_IsLastAssigned(ClaimId)<=1)

Note that check constraints like this would not catch an update to the LastAssignment column. See for example this blog post. You can work around that by passing @LastAssignment as a parameter:

alter FUNCTION FN_IsLastAssigned(@ClaimId INT, @LastAssignment bit)
...
CHECK (dbo.FN_IsLastAssigned(ClaimId, LastAssignment)<=1)

But at the end of the day, SQL Server was not written to support these kind of check constraints. I feel they are better avoided altogether.

Answer 2

You might want to look up in some reliable resource (e.g. the f... manual, or in the SQL standard), when exactly CHECK constraints are verified. And if it turns out to be "after the INSERT has been processed", then you might try to figure out how the count of existing matching rows can ever be zero.

Apart from that, most engines disallow referencing tables in CHECK constraints. It's hopelessly annoying, but there is a very good reason for it !!!. That reason is that the CHECK constraint should also be checked in the event of an update occurring to the referenced table, that somehow impacts the result of this CHECK constraint for some row of the table in which it is declared.

Engines typically do not know how to do this efficiently. So they impose that limitation upon the users so that at least the integrity of the data is not compromised. Do not make the mistake of thinking that you are smart when you discover that a UDF with some SELECT can circumvent the limitation. You're only deluding yourself. And a couple of months later, you'll just be the next one to ask yet another "CHECK constraint not working" question.

Answer 3

A trigger is the proper way to do this. We have a simliar process and when someone sets the value to 1, any existing records that are set to 1 are reset to 0. WHen someone deletes the one that is set to 1, it goes through a chain of rules to determine which remaining record to set to 1 (we must always have one and one one record set to 1). This process is more complicated than a check constraint to handle, thus it is in a trigger (this is the main purpose for triggers even existing).

In a SQl server trigger, you need to be careful not to try to handle only one record at atime. Triggers operate on the entier set of data so make sure to write your trigger to do so as well. A 1,000,000 records might be changed in one update, you don't want to lock up your system while it gores through row by row. So no cursors and no scalar functions and no correlated subqueries.