Take the 2-minute tour ×

I have a table Person with a Code(varchar), a Name(varchar) and Active(bit) fields. When I search for a Person only active records are returned, and when I delete a Person I set Active = false so that it does not show up in my Search.

The problem is I've added a Unique Constraint on the Code and Name fields, so that there can only be one of each Person. Problem is now the User doesn't see the Person in the Search (coz their active is false) and tries to add that same Person but the Unique constraint stops them from doing so.

Is there a way to keep the Unique constraint and maybe add a check to Active so that I can still add that Person if it was previously set as active = false? I know the easy solution here is to just delete the record completely but for data purposes I need to keep the record and not delete it hence only setting active = false.


share|improve this question
Why don't you move archive data to the separated archive table? –  zerkms Sep 12 '11 at 11:50
Can you modify your constraint to include Code, Name, and Active? –  Yuck Sep 12 '11 at 11:50
@Yuck: It will prevent of having several code+name in deleted state (which is still perfectly a valid case) –  zerkms Sep 12 '11 at 11:50
@zerkms: To me those are still the same person even if they've been deleted. Sounds like duplicate data in the Person table if that's allowable. –  Yuck Sep 12 '11 at 11:52
@Yuck: they are archive records. I don't think it is a duplication having several obsolete rows for each person. It just happens when something changes. –  zerkms Sep 12 '11 at 11:55

2 Answers 2

No. If you apply a unique constraint to a field (or fields) then it is prevented from having any duplicate data.

Theoretically, you could apply the unique constraint over all three of your fields, but that would prevent you from being able to 'delete' a person if the same person existed in the table and was already 'deleted' (having active set to false).

I would personally archive the data rather than having an 'active' field if this is your concern. Create a new table and insert the deletes in there to save the data and remove them from the existing 'Person' table.

share|improve this answer

How about...

Remove the constraint, and do a count on the table. Before the insert just do a count on the table, to ensure that there are no duplicate records.

kind regards

share|improve this answer
In this case you need to lock the whole table to get the consistent state, or you'll have race condition issues otherwise. –  zerkms Sep 12 '11 at 12:02
How big will the table be ? –  CaptainOrgange Sep 12 '11 at 12:04
As you stated 2 minutes ago the table will probably not contain more than 200 records at a time, at most. –  Melanie Sep 12 '11 at 12:06

Your Answer


