CODEWITHSUNDEEP
sql-serversql-server-2012
user1679941
user1679941

Reputation:

Is there a way to ensure the contents of a field are either NULL or UNIQUE

I have a SQL Server 2012 database. My table called REFERENCE has a column called LOCATOR.

My users are entering data but sometimes they enter two different rows with the same LOCATOR. Is there a way I can make this create an error that appears only if the LOCATOR is not null ?

Upvotes: 2

Views: 46

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

This is what a UNIQUE constraint does (in most DBMS), not allow 2 rows with same value in a column - while ignoring NULL values.

The problem is that SQL-Server has implemented unique constraints differently than other DBMS and does not allow more than one rows with NULL when a unique constraint or index is defined on a column.

The solution is to use a partial index:

CREATE UNIQUE INDEX Locator_UQ                -- name of the index
ON Reference (Locator)
WHERE Locator IS NOT NULL ;

Minimal test at: SQL-Fiddle

Upvotes: 6

Related Questions