Multiple column constraint with conditions

[LindaH]

How would I set up a constraint for the following scenario:

I've got 2 columns, SSN & an ArchiveFlag. If ArchiveFlag = 1, the row is set to be archived and essentially not considered to be part of the table. If the ArchiveFlag is NULL, the row is active. The constraint would be we can't have duplicate, Active SSN's.

This would be valid:
Row 1: SSN = 123456789 ArchiveFlag = NULL
Row 2: SSN = 123456789 ArchiveFlag = 1
Row 3: SSN = 123456789 ArchiveFlag = 1
Row 4: SSN = 123456789 ArchiveFlag = 1

Trying to add Row 5 would be invalid:
Row 1: SSN = 123456789 ArchiveFlag = NULL
Row 2: SSN = 123456789 ArchiveFlag = 1
Row 3: SSN = 123456789 ArchiveFlag = 1
Row 4: SSN = 123456789 ArchiveFlag = 1
Row 5: SSN = 123456789 ArchiveFlag = NULL

 

[bperry]

Hi LindaH:
When we are talking constraints in this situation, we have two potential choices: a UNIQUE CONSTRAINT and a CHECK CONSTRAINT. Unfortunately, I think both of these are out.

A UNIQUE constraint is no good because SSN is not unique; duplicates are allowed.

A table-level CHECK constraint is no good, because (I believe) the values to be checked must be within the same current row. And they are not, because we want to check other records to validate our entry.

However, perhaps you can consider an Insert trigger on your table. The trigger would fire automatically whenever a new record was inserted; it could check to see if SSN already exists, with a NULL ArchiveFlag, and do a ROLLBACK if one was found. The trigger would look something like this:
[tt]
CREATE TRIGGER CheckSSN ON dbo.LindaTable
FOR INSERT
AS

IF EXISTS
(Select 'True'
From Inserted i LEFT JOIN LindaTable lt
ON i.SSN = lt.SSN
Where lt.ArchiveFlag IS NULL)
BEGIN
RAISERROR('That SSN is already active',16,1)
ROLLBACK
END
[/tt]
----------------------------
(To perform well, there should be an index on SSN, which I'm sure there probably is.)

So consider that approach if you want.
You would also have to consider Update transactions; if users can update the SSN column, then you need to modify the trigger example to handle updates (or add a new Update trigger.)

 

[bperry]

Hi again,
I found after some testing that the Insert trigger I supplied yesterday did not work all that well. This code should work better. (Of course, lots of folks are better at writing triggers than I am, so go ahead and modify/adapt to your own situation.)
--------------------------------------
CREATE TRIGGER CheckActiveSSN ON dbo.LindaTable
FOR INSERT
AS
BEGIN
IF (Select COUNT(lt.SSN)
From inserted i INNER JOIN LindaTable lt
ON i.SSN = lt.SSN
Where lt.ArchiveFlag IS NULL)
> 1
BEGIN
RAISERROR('That SSN is already active',16,1)
ROLLBACK
END
END
----------------------------

 

[bperry]

And Hi once more:
In the earlier responses we were talking about the Insert trigger. Here's the Update trigger if you're interested: it's a little more complicated because we might be updating more than one SSN, and they each need to be checked to make sure there's not a matching active one already.

CREATE TRIGGER UpdateActiveSSN ON dbo.LindaTable
FOR UPDATE
AS

IF
(
Select COUNT(lt.SSN) as SSNcount
from inserted i INNER JOIN LindaTable lt
ON i.SSN = lt.SSN
where lt.ArchiveFlag IS NULL
Group By i.SSN
Having Count(lt.SSN) > 1
)

> 0

BEGIN
RAISERROR('Cannot update.... SSN already active',16,1)
ROLLBACK
END
-------------------------------------
This seems to work fine on my system, but as always, test thoroughly in your environment before releasing.


I notice that this new Update trigger also seems to handle the Insert situation satisfactorily too, so perhaps you don't need both the Insert and Update triggers together. The one Update trigger could handle both cases by simply changing UPDATE above to UPDATE, INSERT


My apologies for these overly-long responses. I didn't have a good feel for how much you knew about triggers. No doubt you may know more about them than I do, so fix/modify/improve however you see fit. (Or, you may decide you do not like the Trigger approach anyway.)

rgrds, etc
bperry