| Author |
 Topic  |
|
|
pnkFloyd
Starting Member
Lithuania
8 Posts |
 Posted - 04/19/2005 : 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
United Kingdom
583 Posts |
Posted - 04/19/2005 : 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 - 04/19/2005 : 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
United Kingdom
583 Posts |
Posted - 04/19/2005 : 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 |
Edited by - AndyB13 on 04/19/2005 13:36:26 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 04/19/2005 : 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
|
Edited by - X002548 on 04/19/2005 13:58:14 |
|
|
|
AndyB13
Aged Yak Warrior
United Kingdom
583 Posts |
Posted - 04/19/2005 : 13:50:13
|
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 |
|
|
|
pnkFloyd
Starting Member
Lithuania
8 Posts |
Posted - 04/20/2005 : 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
Australia
1591 Posts |
|
|
pnkFloyd
Starting Member
Lithuania
8 Posts |
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 04/20/2005 : 03:34:42
|
you shouldn't be using NULLs anyway. Avoid them and you avoid issues like this.
:)
-ec |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/20/2005 : 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 |
|
|
ahmeds08
Constraint Violating Yak Guru
India
430 Posts |
Posted - 11/14/2010 : 23:51:41
|
| the only way to allow repeated data to be inserted into the table is to drop the unique constraint. |
|
|
| |
Topic |
|
Can't insert NULL into table with unique key
