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

constraintsql-server-2005

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
    )

Best Answer

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.

Related Question