Aug 1, 2013

Sql server create unique constraint which allow multiple NULL values.

Suppose we want to create a table with unique constraint which allow multiple null values

Step 1: Create the table

CREATE TABLE tblEmployee(
    ntID BIGINT IDENTITY PRIMARY KEY,
    ntKey INTEGER
)

Step 2: Create unique filtered nonclustered index on it:

CREATE UNIQUE NONCLUSTERED INDEX NCI_Unique_Key
ON tblEmployee(ntKey)
WHERE ntKeY IS NOT NULL

Step 3: Test it:

a. Inserting multiple NULL value into the column ntKey (It will insert)

INSERT tblEmployee DEFAULT VALUES

b. Inserting duplicate not null value into the column ntKey (It will not insert)

INSERT tblEmployee VALUES(1)
INSERT tblEmployee VALUES(1)

No comments:

Post a Comment