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)
 Can't insert NULL into table with unique key

Author  Topic 

pnkFloyd
Starting Member

8 Posts

Posted - 2005-04-19 : 13:21:59
Hi,
I have created very simple table with 3 columns (SQL2000 server):

CREATE TABLE test (
id smallint IDENTITY (1, 1) NOT NULL ,
code1 varchar (15) COLLATE Lithuanian_CI_AS NOT NULL ,
code2 varchar (14) COLLATE Lithuanian_CI_AS NULL
GO
ALTER TABLE test WITH NOCHECK ADD
CONSTRAINT PK_test PRIMARY KEY CLUSTERED (id)
GO
ALTER TABLE test ADD
CONSTRAINT UK_test_code1 UNIQUE NONCLUSTERED (code1),
CONSTRAINT UK_test_code2 UNIQUE NONCLUSTERED (code2)
GO

The table has few records:

INSERT INTO test (code1,code2) VALUES ('11','AA')
GO
INSERT INTO test (code1,code2) VALUES ('12','AB')
GO
INSERT INTO test (code1,code2) VALUES ('13',NULL)
GO

When I try to insert one more record with NULL value

INSERT INTO test (code1,code2) VALUES ('14',NULL)

I get error message:

Error: Violation of UNIQUE KEY constraint 'UK_test_code2'. Cannot insert duplicate key in object 'test'. (State:23000, Native Code: A43)


Why I can not insert more than one record with NULL values? I have tried to change constraint to index but it didn't help.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-19 : 13:25:19
UNIQUE!!!!!
You are only allowed 1 of each "value", so NULL can only be in there ONCE

Look it up in BOL

Andy

Beauty is in the eyes of the beerholder
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-19 : 13:32:00
I bet you have a DB2 or Oracle background where this allowed.


I'll bet your asking yourself:

"Since Null is the absence of a value, and since it's not equal to anything, even itself, how can it be a dup?"

Them's the cards as they have been dealt from the SQL Server Deck.

I wonder if SQL 2005 will allow UNIQUE WHERE NOT NULL



Brett

8-)
Go to Top of Page

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-19 : 13:35:21
quote:
I have tried to change constraint to index but it didn't help

This works, did you drop the constraint before creating the index??

CREATE TABLE test (
id smallint IDENTITY (1, 1) NOT NULL ,
code1 varchar (15) NOT NULL ,
code2 varchar (14) NULL)
GO
ALTER TABLE test WITH NOCHECK ADD
CONSTRAINT PK_test PRIMARY KEY CLUSTERED (id)
GO
ALTER TABLE test ADD
CONSTRAINT UK_test_code1 UNIQUE NONCLUSTERED (code1)
GO
CREATE NONCLUSTERED INDEX IndexNameHere ON test (code2)
GO

INSERT INTO test (code1,code2) VALUES ('11','AA')
GO
INSERT INTO test (code1,code2) VALUES ('12','AB')
GO
INSERT INTO test (code1,code2) VALUES ('13',NULL)
GO
INSERT INTO test (code1,code2) VALUES ('14',NULL)
GO

SELECT * FROM test
GO

DROP TABLE test


Beauty is in the eyes of the beerholder
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-19 : 13:42:10
But that will allow dups in code2



Brett

8-)

EDIT: And I might I add, as a first post, that's the way to go. Thanks

EDIT2: I trying to come up with a hack using a TRIGGER



Go to Top of Page

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-19 : 13:50:13
To quote Andy Pipkin from Little Britain
Yeah, i know

[url]http://www.lou-and-andy.com/soundboard/[/url]

He could create a UNIQUE CONSTRAINT/INDEX across code1 & code2 instead. As code1 is unique and does not allow NULL

Andy

Beauty is in the eyes of the beerholder
Go to Top of Page

pnkFloyd
Starting Member

8 Posts

Posted - 2005-04-20 : 01:56:47
quote:

He could create a UNIQUE CONSTRAINT/INDEX across code1 & code2 instead. As code1 is unique and does not allow NULL


Yes but that will allow dups in code2. I assume I'll have to use triger
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-04-20 : 02:14:27
Brett doesn't like this method.. so here it is.. :-)

[url]http://weblogs.sqlteam.com/davidm/archive/2004/05/21/1364.aspx[/url]


DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

pnkFloyd
Starting Member

8 Posts

Posted - 2005-04-20 : 03:15:15
quote:

http://weblogs.sqlteam.com/davidm/archive/2004/05/21/1364.aspx


Nice solution, thank you!
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-04-20 : 03:34:42
you shouldn't be using NULLs anyway. Avoid them and you avoid issues like this.



:)


-ec
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-04-20 : 07:22:43
You could make a VIEW and put a UNIQUE INDEX on that - but I don't recommend it - all sorts of DML SET commands have to be in place to use it, and you have to rejig the VIEW when the table changes. I've done it once and vowed "never again"!

Personally I would use a TRIGGER to enforce the uniqueness.

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-20 : 13:28:50
quote:
Originally posted by byrmol

Brett doesn't like this method.. so here it is.. :-)

[url]http://weblogs.sqlteam.com/davidm/archive/2004/05/21/1364.aspx[/url]


DavidM

A front-end is something that tries to violate a back-end.



I didn't say I dodn't like it...

I just prefer something like

http://weblogs.sqlteam.com/brettk/archive/2005/04/20/4592.aspx



Brett

8-)
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-11-14 : 23:51:41
the only way to allow repeated data to be inserted into the table is to drop the unique constraint.
Go to Top of Page
   

- Advertisement -