Re: create unique index to allow null values

Article View: comp.databases.ms-sqlserver

Article #100

Back to comp.databases.ms-sqlserver Previous Next

#100

From: Erland Sommarskog
Date: Wed, 16 Apr 2003 10:35

26 lines
2450 bytes

Jobi (johan.bijnensNo_Spa_M@ugine-alz.arcelor.com) writes:
> It 's not a good idea to allow NULLs on a column that should be part of
> a unique index, because in that case with sqlserver NULL = NULL and
> therefor there can be only one. 

And sometimes this is what you want. 

Sometimes it is not, and then you need to implement the constraint in
some other way.

One is to use a trigger.

Another is to create an view over the non-NULL values, and then add a unique 
index on the view, and it is this the technique that the original is using.

Just for the record, in the database I have been working with for the last
years, I have tables where only one NULL value is permitted in the unique
index (that column is typical a second or third column in the natural 
primary key). And I have tables where the rule is unique-when-not-null. 
Of historic reasons I cannot use indexed views, so I use triggers instead.

-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Technical Details

Message-ID: <Xns935F7FF11CBB0Yazorman@127.0.0.1>

Path:

arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!newsfeed.stanford.edu!logbridge.uoregon.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!news.algonet.se!algonet!news2.tninet.se!not-for-mail

References: <b7ito9$5kt$1@ctb-nnrp2.saix.net> <b7j5bv$lbr$1@reader10.wxs.nl>

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 1135f7,32f0907ad6165bb4
X-Google-Attributes: gid1135f7,public
X-Google-Thread: 103584,32f0907ad6165bb4
X-Google-Attributes: gid103584,public
X-Google-Thread: 104de1,32f0907ad6165bb4
X-Google-Attributes: gid104de1,public
X-Google-Thread: ff490,32f0907ad6165bb4
X-Google-Attributes: gidff490,public
X-Google-ArrivalTime: 2003-04-16 03:34:30 PST
Path: arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!newsfeed.stanford.edu!logbridge.uoregon.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!news.algonet.se!algonet!news2.tninet.se!not-for-mail
From: Erland Sommarskog <sommar@algonet.se>
Newsgroups: comp.databases.ms-sqlserver,cz.comp.microsoft.sql,microsoft.public.za.sql,microsoft.public.za.sql.setup
Subject: Re: create unique index to allow null values
Followup-To: comp.databases.ms-sqlserver
Date: Wed, 16 Apr 2003 10:35:42 +0000 (UTC)
Organization: BRF Beckasinen 13
Lines: 26
Sender: sommar@algonet.invalid
Message-ID: <Xns935F7FF11CBB0Yazorman@127.0.0.1>
References: <b7ito9$5kt$1@ctb-nnrp2.saix.net> <b7j5bv$lbr$1@reader10.wxs.nl>
NNTP-Posting-Host: kairos.algonet.se
X-Trace: green.tninet.se 1050489342 1537 213.150.135.237 (16 Apr 2003 10:35:42 GMT)
X-Complaints-To: abuse@telenordia.se
NNTP-Posting-Date: Wed, 16 Apr 2003 10:35:42 +0000 (UTC)
User-Agent: Xnews/5.04.25

Reply to Parent Message