UNIQUE CONSTRAIN problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UNIQUE CONSTRAIN problem

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-05-22 : 15:50:12

Hello all...

I have a field called LOGON.

I would like to prevent duplicate values from being entered AND allow nulls on this field.

I thought about adding a unique constraint but I get the following error:

CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 24. Most significant primary key is '<NULL>'.

Here is how I'm trying to create it:
ALTER TABLE dbo.mastertable ADD CONSTRAINT
IX_mastertable UNIQUE NONCLUSTERED
(
LOGON
) ON [PRIMARY]

Any other ideas?

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-05-23 : 05:09:09

Although NULL is unknown and so NULL<>NULL, for the purposes of Unique Indexes SQL Server treats NULL as NULL=NULL (dubious behaviour to say the least)thus you can only have one NULL in the column (yet another reason to not allow nulls). If its a composite index across a number of columns then you can null many times as long as each column combination is unique e.g.

col1 col2
NULL NULL
0 NULL
NULL 0
1 NULL
NULL 1

HTH
Jasper Smith

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-05-28 : 08:04:42

Create an auto increment field in the table and either use that as a primary key / unique identifier or update the null values in the LOGON field with something sensible.

Check for duplicates by

select LOGON, count(*)
from mastertable
group by LOGON
having count(*) > 1

or

select mt.* from
mastertable mt left outer join (select LOGON, count(*)
from mastertable
group by LOGON
having count(*) > 1) qt on mt.LOGON = qt.LOGON

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources