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
Subscribe to:
Post Comments (Atom)
3 comments:
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
Excellent. Love the nullbuster solution.
Post a Comment