unique key with optional field

[HandersonVA]

Is it possible to have the field as a unique key and a optional one?
It is like.. for example, office code has to be unique (cannot be
duplicated with the same code) and it could be null too.

[David Portas]

Here are three possible methods (in each case, Colx is the nullable, unique
column):

1. Use an indexed view:

CREATE VIEW SomeTable_Unique_Non_NULL
WITH SCHEMABINDING
AS
SELECT colx
FROM dbo.SomeTable
WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL (colx)

2. Use an index with a calculated column:

CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL, foo
AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))

3. Use a trigger:

CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
ON SomeTable FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT *
FROM Inserted AS I
JOIN SomeTable AS S
ON I.colx = S.colx AND I.keycol<>S.keycol)
BEGIN
ROLLBACK TRAN
RAISERROR('Duplicate values not permitted',16,1)
END

--
David Portas
SQL Server MVP
--