If you primarily employ logical deletes and very rarely actually remove a row from a table, a traditional unique constraint often results in a bad user experience. Consider the following scenario:
Let’s say I have a table called [Color] with a unique constraint on the [Name] column.
User1 adds the color “Red”.
User2 decides “Red” is a dumb color and deletes it (which merely sets the [IsActive] flag to False).
User1 then sees “Red” is missing and attempts to add it in again.
The system tells him he can’t add “Red” because it already exists.
User1 says, “!&#^@%”, because “Red” clearly doesn’t exist.
The solution is a filtered unique index.
CREATE UNIQUE NONCLUSTERED INDEX [UIX_Color_Name] ON [Client].[Color] ( [Name] ASC ) WHERE ([IsActive] = 1)
The key here is the WHERE clause. Now when User1 attempts to add “Red”, the system will only consider the active records when checking for uniqueness. The result is that I can have multiple inactive records for a given value, but only one active record.
To handle this in the calling code, enclose your insert statement in a Try…Catch (SqlException ex). When the unique filtered index is violated, SqlException.Number will be 2601. Check for that number in your Catch and craft a friendly message accordingly.