UNIQUE constraint with multiple NULLs
-
Why does SQL Server not allow multiple NULLs for a column with a unique constraint defined? As far as I understand from SQL-99, although "NULL duplicates NULL" (but "NULL never equals NULL"), a UNIQUE constraint specifically allows multiple null values. This is not the case in SQL Server 2000, but in Oracle it works that way.
Try this example to see what I mean:
create table nullodule (a int null)
alter table nullodule add constraint u_null unique (a)
insert into nullodule (a) values (1)
insert into nullodule (a) values (null)
insert into nullodule (a) values (2)
insert into nullodule (a) values (null)
insert into nullodule (a) values (3)
select * from nulloduleMy theory is that the problem in SQL Server is the unique index created and used to maintain the UNIQUE constraint. Any thoughts?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
http://www.sql.nu
-
This is just my two cents, but I think the statement :"NULL duplicates NULL" (but "NULL never equals NULL") is absolutely true, but is being interpreted differently than it was meant. If taken literally, Null duplicates Null indicates that the two states evaluate to be the same. By saying: (but "NULL never equals NULL") your not invalidating the first, but clarifying the evaluation. In other words, Null is actually a duplicate of Null but if evaluted by an = sign, will not show the same.
That's how I read it, anyway. And proves to be true by your example. It may be that I'm just used to looking at it this way due to working with it daily and knowing how Sql Server handles it, but this is the behavior I would expect.
-
I concur with Scorpian.
Steve Jones
-
I think you are just seeking a discussion topic, but just in case you are looking for a solutions.
You could write a trigger to allow only unique non-Null values.
-
Paul, you are absolutely correct, I am just seeking the discussion. And a trigger is the solution I would recommend as well. Now, I may have been unclear, because I mean exactly what Scorpion and Steve said. For instance, that is why a select with a GROUP BY clause creates just a single group for NULLs (since NULL duplicates NULL), but an equality comparison to NULL is always UNKNOWN. The question I was wondering about, or rather subject to the discussion, was why SQL Server does not allow multiple NULLs in a column where a UNIQUE constraint is defined, when it is clearly stated in SQL-99 that multiple NULLs should be allowed, and other RDBMS (Oracle at least) in fact does allow them.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Sometimes we just have to accept the behavior they provided and hope it is not going to change in the next version.
I would avoid using trigger as hard as I can. In the worst case, I would make the column NOT NULL DEFAULT 0. Of course, zero and NULL are different creatures (and zero takes space) but the goal might be achieved (depending on the business rules).
Michael
-
In that case, I would believe that there are different definitions for Unique between the systems. Unique by any definition I ever heard was defined as "Having No duplicates", or "Having a single occurrance".
This would lead me to believe that in Oracle, Unique constraints are evaluated by equals (as a value) rather than by pattern matching or hash (which can do states). I would see this as a problem unless Oracle dismissed the "NULL duplicates NULL" part of the statement, or interpreted it in a different manner. It seems that Oracle has a dual standard if it doesn't dismiss the statement, in that it evaluates the data in one manner, and the constraint of the data in another. And if it does dismiss the statement, I would view that as an issue in that it doesn't behave as stated. Either way, I believe it's inconsistent. Of course, I am not flaming Oracle, as I like it a lot, and being aware of how it acts will allow you to accomodate the behavior, just like some of the inconsistencies in SQL. I just don't belive its accurate in this regard.
-
But it is SQL Server, not Oracle, that is incorrect in the way it handles UNIQUE constraints. SQL-99 states that they should allow multiple NULLs. A NULL is never equal to anything, not even another NULL, so therefore many NULLs can exist in a UNIQUE constraint, since they are never equal.
As mromm said, this is probably just the way it is in SQL Server. I was just looking for thoughts and comments on why it is this way in SQL Server, or maybe if I had missed something. I really dislike all differences from the standards, especially when it is stuff that doesn't really matter, so I would actually be glad if they changed it in a futrue version (of course with a nice SET-config to be able to use the old mode).
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Where does Sql-99 state that it should allow multiple nulls in a unique constraint? That contradicts the "NULL duplicates NULL" (but "NULL never equals NULL") statement.
Your example shows that the unique constraint actually gives unique values in SQL Server, and you state that in Oracle, a unique constraint does not give unique values. How is it that SQL is handling it incorrectly?
The way I figure it, if a unique constraint gives unique values, thats correct behavior, and if it doesn't thats incorrect behavior.
I think they're getting the basis of null being a state, not a value, mixed up. A null is a state of non-existence, not an actual value, so of course it cannot equal anything even another null. However, this does not mean that 2 nulls are not the same thing. Hence, two nulls would violate a unique constraint if in fact they require uniqeness. You can demonstrate this by selecting distinct values on any table in Oracle containing nulls, constraint or not. It will only return a single null in the record set, not a full list of them. A unique constraint, in my mind should allow the same thing that a select distinct would return. Is this not true?
Edited by - Scorpion_66 on 12/19/2002 2:33:00 PM
-
First of all, the "NULL duplicates NULL" (but "NULL never equals NULL") statement was my own words, and maybe badly written. A better way to write it would be (yes it is wrong to say 'a NULL value', since it is not a value, but it's how everyone talks about them):
A NULL value is never equal to a NULL value. However, a NULL value is a duplicate of a NULL value.
A quote from Dr E. F. Codd:
"Identification for duplicate removal is at a lower level of detail than equality testing in the evaluation of retrieval conditions. Hence it is possible to adopt a different rule."
This is why GROUP BY, DISTINCT and UNION (plus INTERSECT and EXCEPT that don't exist in SQL Server) treat multiple NULLs as 'equals' (duplicates).
Now, I don't have a copy of the actual SQL-99 standard, but I do have some books describing it, and they say that both the UNIQUE predicate (which does not exist in SQL Server) and UNIQUE constraints allow multiple NULLs. For example, a series of rows containing {1,NULL,2,NULL,3} is UNIQUE. So, the question now, is if these books are correct or not (hopefully someone here has access to the standards), and if SQL Server is correct or not in the way it handles UNIQUE constraints.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
I know you didn't want a solution but I saw this which may be of interest to someone: -
http://www.microsoft.com/sql/techinfo/tips/development/ensuringnonnull.asp
Regards,
Andy Jones
Edited by - andyj93 on 12/20/2002 02:47:30 AM
.
-
Aha! The definition in SQL-99 is this:
"A unique constraint is satisfied if and only if no two rows in a table
have the same non-null values in the unique columns"
So, null values don't should not break the unique constraint. So, SQL Server does not handle this according to the standard.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Ahhh, now that makes more sense. And in that case, I finally see why you believe that multiple nulls SHOULD be allowed. And now I can't help but believe your right about SQL Server handling it incorrectly. By that definition, it does.
That does make it quite interesting though. Blast it, Now you make me EXTREMELY curious as to WHY microsoft did it that way, too. It makes sense from a logical point of thinking in that it creates consistency in handling the constraints the same as the data, but if it's not the standard, they HAD to have a reason for going against it that way. I know that SQL's constraints are not evaluated by comparison operators, but by hash comparisons, so maybe that has something to do with it.
-
Haha, finally someone that find this as interesting as I do.
Your theory with the hash comparisons sounds reasonable.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Is it a unique constraint or is it a primary key also. If the later check out this from BOL
quote:
UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values.Also, depending on the version of SQL the server may be getting you with ANSI_NULLS being on. Check your server and DB settings. For more details on this check out the following article.
http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply