Sunday, March 11, 2007

UNIQUE Constraint vs NULL's

This is another well known question get asked , how to insert more than one NULL value in the column which has an unique constraint defined on?

Ok, you can create a view WITH SCHEMABINDING that issues SELECT column FROM Table WHERE column IS NOT NULL. Then you need to create an unique index on view (column).

Another method I learned from Steve Kass a long time ago and would like to publish here is that we create a computed column called nullbuster that based on primary key and another column. Now that we have this column so we create a UNIQUE Constraint on two columns. See how it works.

CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)

INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls

6 comments:

haiyan said...

When I logged into the hotel, I was surprised that I had 1000 habbo credits which is unbelievable. I logged back in 5 minutes later to discover that all my habbo gold has been stolen. It gives you a virtual environment where you can use your imagination to create your own small cartoony world with your own habbo coins and interact with different people. For decorations you actually pay real monry to buy habbo gold in the range of $0.20, it may sound ridiculous. To use Hotel navigator you need to install cheap habbo credits of macromedia hockave player. There are two types of rooms, public and guest room.

shh said...

Here is a Guid (surrogate key) version, just to save (a future reader) a little time.

Yes, I know there are cons to using GUID's as primary keys.

But sometimes they make sense.


----START TSQL


CREATE TABLE GuidNullBuster (
SurrogateUUID uniqueidentifier primary key not null default newsequentialid() ,
ShouldBeUniqueOrNullUUID uniqueidentifier NULL,
NullBusterUUID as (case when ShouldBeUniqueOrNullUUID is null then SurrogateUUID else '00000000-0000-0000-0000-000000000000' end),
CONSTRAINT NullBusterContraintsForShouldBeUniqueOrNullUUID UNIQUE (ShouldBeUniqueOrNullUUID,NullBusterUUID)
)

INSERT INTO dbo.GuidNullBuster (SurrogateUUID, ShouldBeUniqueOrNullUUID) values ( 'AAAAAAAA-0000-0000-0000-000000000011' , 'BBBBBBBB-0000-0000-0000-000000000021' )
INSERT INTO dbo.GuidNullBuster (SurrogateUUID, ShouldBeUniqueOrNullUUID) values ( 'AAAAAAAA-0000-0000-0000-000000000012' , NULL )
INSERT INTO dbo.GuidNullBuster (SurrogateUUID, ShouldBeUniqueOrNullUUID) values ( 'AAAAAAAA-0000-0000-0000-000000000013' , NULL )

Select * from dbo.GuidNullBuster ORDER BY SurrogateUUID


--Should Fail
UPDATE dbo.GuidNullBuster SET ShouldBeUniqueOrNullUUID = 'BBBBBBBB-0000-0000-0000-000000000021' WHERE NullBusterUUID = 'AAAAAAAA-0000-0000-0000-000000000012'

--Should Fail
INSERT INTO dbo.GuidNullBuster (SurrogateUUID, ShouldBeUniqueOrNullUUID) values ( 'AAAAAAAA-0000-0000-0000-000000000019' , 'BBBBBBBB-0000-0000-0000-000000000021' )

--Should be Ok
UPDATE dbo.GuidNullBuster SET ShouldBeUniqueOrNullUUID = 'BBBBBBBB-0000-0000-0000-000000000031' WHERE NullBusterUUID = 'AAAAAAAA-0000-0000-0000-000000000012'

DROP TABLE dbo.GuidNullBuster

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

Sean Brown said...

Excellent. Love the nullbuster solution.

aiya said...
This comment has been removed by a blog administrator.