Why Unique Column will not allow Multiple NULL Values ?

Question

Why Unique Column will not allow Multiple NULL Values ?

mahesh.shiv

SSC Enthusiast

Points: 159
More actions
January 9, 2013 at 10:30 pm

#268605

Since One NULL is not Equal to Another NULL(NULL<>NULL), But still why we are not allowed to insert multiple NULL values into an Unique column ..

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

That is the way it was designed in SQL Server.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ant-Green SSC Guru Points: 113499 More actions · Answer 2

There is a way around this, but it will NOT add any null values into the index, so if you constaintly use the column for queries which relies on the index and NULL values, it is not recommended.

But create the unique constraint filtered so that it excludes NULL values.

But as Jason has said, its the way it is designed, as NULL is Unknown, so 2 NULLs will still be Unknown Unknown so you can't have the value twice.

nagesh.prasad SSC Veteran Points: 291 More actions · Answer 3

Hi Mahesh,

As said above Null is something unknown and another Null is also something unknown. So unique column will not allow two Null.

I guess you lost your bet (100 Rs.) 🙂

Regards,

Nagesh Prasad

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

There is another means to permit multiple nulls in a column in a unique constraint. Create the constraint on multiple columns. As long as the combination from multiple columns is still unique you can insert multiple nulls into a column in a constraint.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

arnipetursson SSCertifiable Points: 6557 More actions · Answer 5

You have to remember that NULL in SQL means two things:

1. Empty set

2. Undefined set.

This is an well know set algebra bug in SQL.

mahesh.shiv SSC Enthusiast Points: 159 More actions · Answer 6

mahesh.shiv

SSC Enthusiast

Points: 159

January 10, 2013 at 8:55 pm

#1576058

Thanks ..

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

arnipetursson (1/10/2013)
You have to remember that NULL in SQL means two things:
1. Empty set
2. Undefined set.
This is an well know set algebra bug in SQL.

NO , NUll is not empty. undefined means you dont know

Null and empty both are difference

See the code

declare @t table (id int identity, name char(3))

insert into @t (name)

select 'A' union

select '' union --blank

select null --null

select * from @t where name = ''

select * from @t where name is null

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

arnipetursson SSCertifiable Points: 6557 More actions · Answer 9

Bhuvnesh (1/11/2013)
arnipetursson (1/10/2013)
You have to remember that NULL in SQL means two things:
1. Empty set
2. Undefined set.
This is an well know set algebra bug in SQL.
NO , NUll is not empty. undefined means you dont know
Null and empty both are difference
See the code
declare @t table (id int identity, name char(3))
insert into @t (name)
select 'A' union
select '' union --blank
select null --null
select * from @t where name = ''
select * from @t where name is null

That is an empty string, not and empty set. An empty set for example is the null you can get back on an outer join.