Unique Indexes and null values

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

SQL Server Development (2000)

Unique Indexes and null values

Author

Topic

blakmk
Starting Member

45 Posts

Posted - 2003-04-30 : 08:14:32

When creating unique indexes on column it seems that the index will only accept one null value.

This test case illustrates this:

create table mb_test1 (x int not null, y int)

alter table mb_test1 ADD PRIMARY KEY NONCLUSTERED (x)

CREATE UNIQUE INDEX XAK_Y ON mb_test1(y)
go

insert into mb_test1 values (1, null )

insert into mb_test1 values (2, null )

select * from mb_test1

Does anyone know a way of having a unique index that accepts multiple null values?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-30 : 09:05:11

You're thinking of a unique constraint

quote:

UNIQUE Constraints
You can use UNIQUE constraints to ensure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of:

A column, or combination of columns, that is not the primary key.
Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.

A column that allows null values.
UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values.

A UNIQUE constraint can also be referenced by a FOREIGN KEY constraint.

quote:

Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. If uniqueness must be enforced to ensure data integrity, create a UNIQUE or PRIMARY KEY constraint on the column rather than a unique index.

You can have a clustered primary key of x and y
or
y can be have an index and a unique constraint

Edited by - ValterBorges on 04/30/2003 09:06:04

Edited by - ValterBorges on 04/30/2003 09:11:01

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 09:08:53

quote:

Does anyone know a way of having a unique index that accepts multiple null values?

If it can accept multiple null values, then by definition it can't be unique !!

Kind of like asking: is there a way to make it so SQL Server will evaluate "1 + 2" as 4 ?

(It's surprising how many times logic like that is a "requirement" for a project!)

- Jeff

Edited by - jsmith8858 on 04/30/2003 09:10:09

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-30 : 09:24:14

quote:
If it can accept multiple null values, then by definition it can't be unique !!

But Jeff: A null is not a value, it is just a placeholder for something unknown, so I dont see any reason why it should not allow multiple nulls. If the value is not known, then how can it determine whether it is a duplicate or not?

(Just trying to learn

)

OS

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-30 : 09:29:02

Ask yourself that question in reverse how can it determine if it's unique?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-04-30 : 09:31:29

This thread is worth a read:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20523
It's got Joe Celko, a link to an interesting MS KB article and everything.

Edited by - Arnold Fribble on 04/30/2003 09:33:25

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-30 : 09:52:44

Wow! thanks a lot ...guess i'm not the only one staring at the sun

gr8 KB article, excellent stuff!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources