Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
    )
share|improve this question
    
Just out of curiosity, have you considered an INSTEAD OF trigger that simply updates all existing rows for the affected claim to 0 if the new row has it set to 1? This is particularly easy if only the newest row should have the flag set, but otherwise it seems you need business logic to determine which row wins - the new one with the flag set or the old one with the flag set. And if old "wins" does this mean the new row gets inserted with 0 or fails. –  Aaron Bertrand Jul 5 '12 at 21:20
    
It's not really a check to see which one should win. Its a check that says fail the insert/update if there is already a record for that claim that has LastAssigned=1. Does that make sense? –  pamozer Jul 5 '12 at 21:30
    
So if someone wants to add a new row associated with the claim and wants to set the flag to true, how do they do it? Insert first and then update? Seems like you could control this by simply ignoring that value on insert and only allowing it on update (even for the first row of a claim, since it seems like your users might not know that any such row already exists). Or again, by using a trigger to only allow it if no such row already exists. In a lot of cases I find triggers a lot easier to manage than constraints implemented as UDFs. –  Aaron Bertrand Jul 5 '12 at 21:34
    
What about having a FK in your claims table pointing out the LastAssignment for that claim? If you do it like that "there can be only one" for each claim. –  Mikael Eriksson Jul 5 '12 at 22:24
3  
add comment

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks. I think we will hold off on this for now. –  pamozer Jul 5 '12 at 22:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.