Can't insert NULL into table with unique key

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

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

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

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

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

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

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.

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!

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

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

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources