Nulls allowed in UNIQUE column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Nulls allowed in UNIQUE column?

Author

Topic

Jimbo2
Starting Member

14 Posts

Posted - 2004-01-13 : 13:25:09

Microsoft documentation says one of the differences between a primary key constraint and a unique key constraint is that null values are allowed in a column with a unique constraint. However, I am not able to load duplicate null values into a column with a unique constraint.

For example:
create table #test1
(field1 char(15) not null constraint pk_field1 primary key,
field2 char(15) null constraint uq_field2 unique)

insert into #test1
select 1, 1 union all
select 2, null union all
select 3, null

The insert fails with the 3rd record. Have I misunderstood the documentation in thinking null values are allowed with a unique constraint? Or is there something else I need to do to make this work? (I'm using sql server 2000 service pack 2).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 13:53:48

With a UNIQUE constraint, you are allowed one NULL value. To have more than one NULL value in a column where you want uniqueness on non-NULL values:

http://www.microsoft.com/sql/techinfo/tips/administration/ensuringnonnull.asp

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-13 : 13:55:50

Well...BOL say it can allow NULL Values...it doesn't say how many...

More than 1 of the absence of anything I guess is a duplicate...

DB2 has that capability though...I guess SQL Server doesn't...at least I haven't seen it....

I wonder if you could use a CHECK contraint....

Brett

8-)

EDIT: And there you go.....the fastest SQL Warrior Princess fingers in the west...

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-13 : 14:00:05

Unique Keys accept null data in columns, but not a duplicate null column or combination of columns in another record because that violates uniqueness. It still indexes null values and performs the constraint.

You may be confusing the documentation where it is talking about using multiple columns to define a unique key. This is where the Unique Key comes in handy as opposed to the Primary Key, because you may only need a value in a second column when a value in the first column already exists.

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-13 : 14:02:42

I have a hard time with the concept of "Duplicate Null"

How can the absence of anything be a duplicate?

Brett

8-)

Jimbo2
Starting Member

14 Posts

Posted - 2004-01-13 : 14:09:05

I agree. I think this is a bug. The documentation says in several places that the unique constrain applies to the non-null values. NOWHERE does it say it applies to all values as well as non-values.

Anybody know how to file a bug report with Microsoft? I'm suing

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 14:12:20

Jimbo2, did you look at the link that I provided?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-13 : 14:14:39

quote:
Originally posted by Jimbo2

I agree. I think this is a bug. The documentation says in several places that the unique constrain applies to the non-null values. NOWHERE does it say it applies to all values as well as non-values.

Anybody know how to file a bug report with Microsoft? I'm suing

Can you post'em? I can't find that reference....

Brett

8-)

Jimbo2
Starting Member

14 Posts

Posted - 2004-01-13 : 14:16:23

Yes, I did, and thanks for posting it. I still feel this is a workaround to something that should work the way it was described in the documentation. The way it was described in the documentation seems like the intuitive way it ought to work, the way you would expect it to work. But yes, I will have to implement the solution that is referred to in your link. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 14:22:22

It's not that there is a bug in the product, but rather the documentation doesn't explicitly state that you can't have more than one NULL value in a column that has a unique constraint defined on it.

The documentation does NOT state that you can have more than one NULL value in this type of column. So where's the bug?

Tara

Jimbo2
Starting Member

14 Posts

Posted - 2004-01-13 : 14:24:10

Here are 2 references I found in BOL:

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 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.

and elsewhere...

UNIQUE constraints enforce the uniqueness of the values in a set of columns.
No two rows in the table are allowed to have the same not null values for the columns in a UNIQUE constraint. Primary keys also enforce uniqueness, but primary keys do not allow null values. A UNIQUE constraint is preferred over a unique index.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 14:26:33

None of those statements say that they allow more than one NULL value in a column with a unique constraint. It just says that the column can be nullable.

It's not that I am defending MS, it's just that I don't see this as a bug or a problem. MS has provided a solution for the scenarion of unique non-null values and multiple null values in the same column.

Tara

Jimbo2
Starting Member

14 Posts

Posted - 2004-01-13 : 14:32:36

"No two rows in the table are allowed to have the same not null values " This clearly says the constraint applies to not null values.

Think about the way you would want this to work. You have a column for which some values are defined, and for those you want them to be unique. For other rows the value has not yet been defined. This seems (imo) a far more likely scenario than treating the null value as though it were a defined value in terms of evaluating uniqueness.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 14:38:37

Yes it does clearly state that it applies to non-NULL values. But it doesn't say that it applies to NULL values. You can't make the inference.

For values that are not yet defined, a default value is usually used instead of NULLs.

Tara

Jimbo2
Starting Member

14 Posts

Posted - 2004-01-13 : 14:46:24

I totally disagree that non-defined values need to have a default value. Non-defined is exactly what null is for. Anyway, defining a default value will result in duplicate rows.

I guess at this point its my interpretation of the documentation against your, which really amounts to nothing in the end. I think the documentation does not reflect the way the software works and I plan to letting Microsoft know. If I've been confused by it, no doubt others have been too, and Microsoft needs to change it. JMO.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 14:59:21

Just found the documentation for it:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_4bas.asp

It looks like they have updated Books Online on MSDN. I've got the Books Online that has been updated using service pack 3 and it doesn't state it there. But it does in MSDN.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-13 : 23:39:08

BOL of v7.0:

quote:

A unique index cannot be created on a single column or multiple columns (composite index)
in which the complete key (all columns of that key) is NULL in more than one row; these are
treated as duplicate values for indexing purposes.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-01-14 : 00:40:15

The cost is lower on a unique index from the CBO perspective. This is because the CBO knows that it will only have to fetch one row instead of a range scan when using a unique index. Allowing multiple NULLs in a unique index defeats this cost estimate that the CBO performs before to determine the execution plan.

If duplicate NULLS were permitted, it would not be possible to perform the index seek operation because of the duplicates.

Lastly, I have a couple of recomendations:

1. Do not use NULL values ever
2. If you do, do not put NULLs into fixed width datatypes
3. UPgrade to Service Pack 3a asap, you might be susceptible to slammer (there is a post sp2 hotfix as well)
4. Don't use NULLs

-ec

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-01-14 : 04:13:11

deja vu! We've been through this entire discussion before: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20523

Here's the link to the MS KB article that describes a few possible options (okay so they are workarounds): http://support.microsoft.com/default.aspx?scid=kb;en-us;322002

OS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-14 : 07:55:50

you can also do with w/o a trigger with my trick:

it does require a dummy indentity column and a calculated column, but it's a way to do it w/o triggers. Not sure which way is more efficient, but this way is kinda cool and you can do some cool constraints this way:

create table tmp
(SomeCol int, -- this is the "all non-null values must be unique" column
dummyCol int identity,
PKCol as case SomeCol when Null then dummyCol else SomeCol end,
constraint SomeCol_UniqueNonNulls unique (PKCol))

GO

insert into tmp (SomeCol) select null
insert into tmp (SomeCol) select 1
insert into tmp (SomeCol) select 2
insert into tmp (SomeCol) select 2
insert into tmp (SomeCol) select null

select * from tmp

GO

drop table tmp

- Jeff

Jimbo2
Starting Member

14 Posts

Posted - 2004-01-14 : 08:59:01

I'm curious to know why you say never use nulls. Seems they play a perfectly valid role.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources