| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-01 : 23:21:57
|
| Vanessa writes "I'm trying to add a new column that is NULLable and has a UNIQUE constraint:ALTER TABLE tablenameADD columnname VARCHAR(50) NULL UNIQUE;goAccording to SQL documentation, this is allowed:"UNIQUE constraints can be: -Created when the table is created, as part of the table definition. -Added to an existing table, provided that the column or combination of columns comprising the UNIQUE constraint ~contain only unique or NULL values~. A table can contain multiple UNIQUE constraints."Nevertheless, I am getting this error:CREATE UNIQUE INDEX terminated because a duplicate key was found. Most significant primary key is '<NULL>'.If the documentation says I can do it, how come I still get an error?TIA-Vanessa" |
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2002-01-02 : 01:35:07
|
Hmm, so you can assign a unique constraint to a column that allows NULLs. However, if you have multiple rows in your column with the same value, say, NULL, then your constraint is violated and ergo (cool, I get to use latin), you get an error. Basically, if you're adding this column to a table with 1000 rows already in it, all those values will be set to NULL with your SQL. So they won't be unique. So you'll get an error.That's my thought. I think it's right though  Does [columnName] get a value later? Enter the unique values, then set the null constraint after that. |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-02 : 01:36:12
|
Hi Vanessa , Not everything the documentation says is implemented 100%.you can have a ONLY a Single NULL value in Unique column, when you try to insert another record with null value in unique column. Sql server treats will not allow coz another record with a null value is entered , IT MAINTAINS UNIQUENESS WITH NULL VALUES TOO .Therefore, when you are trying to add a unique column there might be multiple records in the table, so Sql server tries putting a null value in every records, which again voilates the unique constraint with NULL value too.I just tried it and found this strange behavious. Sql Server is actually treating null value as a value in case of Unique field.Phew!!!!!!! I think a Major Bug.i tested it on Sql Server 7.0.623Hope someone there will put more ideas in this----------------------------Anything which Doesn't Kills you Makes you Stronger |
|
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2002-01-02 : 01:38:52
|
| Not really a bug. I'd say it's expected behaviour. After all, NULL values are still values (kind of). I mean, try getting a UNIQUE record out of a row when all you know is that "it's one of those 1000 rows where x is NULL"...Just do what I mentioned and don't set the unique constraint until the row is populated and there shouldn't be any troubles. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-01-02 : 01:45:57
|
| HiYou could probably use a trigger or a rule instead of a constraint if you wanted logic such that any values EXEPT NULLS needed to be unique. But I would question the business logic in that.My 2cDamian |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-02 : 01:55:52
|
| This is what BOL says about Null ValuesThe value NULL means the data value for the column is unknown or not available. NULL is not synonymous with zero (numeric or binary value), a zero-length string, or blank (character value). Rather, null values allow you to distinguish between a entry of zero (numeric columns) or blank (character columns) and a nonentry (NULL for both numeric and character columns).-------Added to an existing table, provided that the column or combination of columns comprising the UNIQUE constraint ~contain only unique or NULL values. A table can contain multiple UNIQUE constraints.--------Now when BOL says you can have NULL VALUES in unique constraint , i feel it should allow multiple Null values in the table. But what it does is it treats it as some value, which negates what Null actually is.----------------------------Anything which Doesn't Kills you Makes you Stronger |
|
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2002-01-02 : 02:00:22
|
| I guess it was written by an intern without highly developed grammar skills. From my point of view it's behaving as it should, but the documentation is just poor. Nazim, maybe you should quit your job and become an editor ;-) |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-01-02 : 02:13:53
|
| OKI got it, I just looked this up in Inside SQL Server 2000Columns involved in a unique constraint can be NULL, however where you have a unique index NULL is counted as a value, therefore one record of NULL is equal to another one that is NULL. Now this is dubious because we know that NULL does not equal NULL usually, however this is how it works here.The way around this, is create the unique index on two columns. One column can be NULL as long as the combination of that column and the other one is unique. Make sense ???Damian |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-02 : 02:20:51
|
Clarke, I Love my work , specially coz it gives me some time to visit Sqlteam . Editing would be fun too. but not all are as Generous as U to give me work .Yeah Damian you do make Sense, but Sql Server Doesnt makes any here .if you pick any Database Concepts book, from C.J.Date to Abraham Korth, they teach you that the difference between a Primary key and a Unique key can contain Null Values where as a Primary Key Cannot.----------------------------Anything which Doesn't Kills you Makes you Stronger |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-03 : 14:23:56
|
quote:
Added to an existing table, provided that the column or combination of columns comprising the UNIQUE constraint ~contain only unique or NULL values.
Well you guys seem to have the constraint question handled, so I thought I'd address the grammar issue. The phrase is unique or NULL values not just NULL values. Because of the conjunction OR, the plural applies to either UNIQUE or to NULL but does not have to apply to both. It can also apply to a combination of a unique value and a null value.Have a great day!  --------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be...Edited by - AjarnMark on 01/03/2002 14:25:12 |
|
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2002-01-04 : 13:39:35
|
quote:
The phrase is unique or NULL values not just NULL values. Because of the conjunction OR, the plural applies to either UNIQUE or to NULL but does not have to apply to both. It can also apply to a combination of a unique value and a null value.
Which sounds like you're agreeing with us... The text is saying you can have unique values, OK OR NULL values. So it sounds like you SHOULD be able to have multiple NULL values. But you can't. It should say something like "unique values, including NULL". It should be more clear that regardless of how you combine them, the values have to be unique. Multiple NULL values aren't unique, but one NULL in a table is."never argue with a sicilian when death is on the line" |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-04 : 18:03:51
|
quote:
Which sounds like you're agreeing with us...
Nope, I'm disagreeing with you. Re-read this part.quote:
Because of the conjunction OR, the plural applies to either UNIQUE or to NULL but does not have to apply to both. It can also apply to a combination of a unique value and a null value.
But there are many other areas where I'll agree the "manual" in whatever form is incorrect or has typos. --------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be...Edited by - AjarnMark on 01/04/2002 18:04:26 |
|
|
|
|
Adding a UNIQUE constraint to a NULLable column