SQL Server unique constraint (but only sometimes)
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 whe开发者_如何学JAVAre IsDeleted = false.
Tim
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);
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.
精彩评论