RSS

Create UNIQUE Constraint and allow NULL values

09 May

If you create an unique constraint using

ALTER TABLE tbl ADD CONSTRAINT UK_tbl UNIQUE(possibleNullValueTable);

you have the problem that you can insert only one row with a NULL value.
But if you create a filtered unique index like you can see in the next statement you can insert as many NULL values as you want.

CREATE UNIQUE INDEX UIX_tbl ON tbl ( possibleNullValueColumn ) 
WHERE possibleNullValueColumn IS NOT NULL

Now you can try the follwoing scipt to prove my assumtion:

CREATE TABLE Test(
	Id int IDENTITY(1,1) NOT NULL,
	PossibleNullValueFK int NULL
	CONSTRAINT PK_Test PRIMARY KEY CLUSTERED ( Id ASC ))

CREATE UNIQUE INDEX UIX_tbl ON Test(PossibleNullValueFK) where PossibleNullValueFK IS NOT NULL

INSERT INTO Test (PossibleNullValueFK) VALUES (1)
INSERT INTO Test (PossibleNullValueFK) VALUES (2)
INSERT INTO Test (PossibleNullValueFK) VALUES (NULL)
INSERT INTO Test (PossibleNullValueFK) VALUES (3)
INSERT INTO Test (PossibleNullValueFK) VALUES (NULL)
INSERT INTO Test (PossibleNullValueFK) VALUES (NULL)
-- Executing the next statement raises a UNIQUE Violation Exception.
INSERT INTO Test (PossibleNullValueFK) VALUES (3)
Advertisements
 
Leave a comment

Posted by on May 9, 2012 in SQL

 

Tags:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: