Unique index except one row

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 may contain exactly one copy of each item, but when I insert the ship over and over, this should be fine. How can i do this?

Answer 1

Create a unique filtered unique index:

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

This will ensure uniqueness for any value other than ship.

This works in SQL Server 2008 and later.