SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Can't insert NULL into table with unique key
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnkFloyd
Starting Member

Lithuania
8 Posts

Posted - 04/19/2005 :  13:21:59  Show Profile  Reply with Quote
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

United Kingdom
583 Posts

Posted - 04/19/2005 :  13:25:19  Show Profile  Reply with Quote
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 - 04/19/2005 :  13:32:00  Show Profile  Reply with Quote
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

United Kingdom
583 Posts

Posted - 04/19/2005 :  13:35:21  Show Profile  Reply with Quote
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

Edited by - AndyB13 on 04/19/2005 13:36:26
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/19/2005 :  13:42:10  Show Profile  Reply with Quote
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




Edited by - X002548 on 04/19/2005 13:58:14
Go to Top of Page

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 04/19/2005 :  13:50:13  Show Profile  Reply with Quote
To quote Andy Pipkin from Little Britain
Yeah, i know

http://www.lou-and-andy.com/soundboard/

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

Lithuania
8 Posts

Posted - 04/20/2005 :  01:56:47  Show Profile  Reply with Quote
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

Australia
1591 Posts

Posted - 04/20/2005 :  02:14:27  Show Profile  Reply with Quote
Brett doesn't like this method.. so here it is.. :-)

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


DavidM

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

pnkFloyd
Starting Member

Lithuania
8 Posts

Posted - 04/20/2005 :  03:15:15  Show Profile  Reply with Quote
quote:

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


Nice solution, thank you!
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

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



:)


-ec
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/20/2005 :  07:22:43  Show Profile  Reply with Quote
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 - 04/20/2005 :  13:28:50  Show Profile  Reply with Quote
quote:
Originally posted by byrmol

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

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


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

India
620 Posts

Posted - 11/14/2010 :  23:51:41  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
the only way to allow repeated data to be inserted into the table is to drop the unique constraint.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000