SQL Server unique constraint (but only sometimes)

Question

Hi, Imagine I have a table with 3 columns:

ID (unique ID) Description (varchar 2000) IsDeleted (boolean)

I want to add a unique constraint so that all descriptions are unique. BUT only for active records i.e. IsDelete = false. If a user flags a record as deleted, they can re-add a record with the same description as a deleted record, but not the same as a active record.

So, I only want the constraint to effect record where IsDeleted = false.

Tim

Answer 1

In SQL 2008 you can do this using unique index with a filter:

CREATE UNIQUE NONCLUSTERED INDEX ix_table_UNC ON table(Description) WHERE IsDeleted = 0

Pre-SQL 2008, you'd need to create the unique index on a view of the table selecting only the non-deleted records:

CREATE VIEW dbo.vw_ActiveDescriptions WITH SCHEMABINDING
AS
SELECT Id, Description
FROM Table
WHERE IsDeleted= 0
GO
CREATE UNIQUE CLUSTERED INDEX ix_vw_ActiveDescriptions_UC ON dbo.vw_ActiveDescriptions(Description);

Answer 2

Create a new table for the deleted rows. Then enforce uniqueness with a UNIQUE constraint. You would have to make Description smaller because the maximum key size in SQL Server is 900 bytes.