Unique Index except for one string

Question

In SQL Server I want to create a column in an existing table that must have unique attributes with one exception: "ship". The table can contain exactly one copy of each element but when I insert "ship" again and again it should be fine. How can I manage that?

Answer 1

Create a filtered unique index:

CREATE UNIQUE NONCLUSTERED INDEX IX_YourTable_YourColumn
ON dbo.YourTable(YourColumn)
WHERE ItemName <> 'ship'

This will enforce uniqueness for any value other than ship.

This works in SQL Server 2008 and newer