April 2, 2012 at 10:19 am
Trying to understand the differences between a primary key and a unique key.
Could someone please explain why does a unique constraint allows only 1 null value in SQL server?
April 2, 2012 at 10:30 am
A primary key can not have a null value. However, for a unique constraint, a single null value is unique. This is why a single null is allowed. If you attempted to add a second null value to a column with a unique constraint, it would no longer be unique.
April 2, 2012 at 10:34 am
Tried to google this and I found that Oracle allows multiple null values in a column with a unique constraint. So, I got curious if there's really a reason behind 1 null value in SQL server.
April 2, 2012 at 10:36 am
Oracle <> SQL Server
Best way to put that one. Also, currently in Oracle an empty string ('') is treated as null while in SQL Server it is not.
April 2, 2012 at 10:43 am
Lynn, I totally accept what you said "If you attempted to add a second null value to a column with a unique constraint, it would no longer be unique.". I also understand that Oracle<>SQL server, but is Oracle really dumb 😉 by allowing more than 1 null value for a unique constraint?
April 2, 2012 at 10:51 am
sunny.tjk (4/2/2012)
Lynn, I totally accept what you said "If you attempted to add a second null value to a column with a unique constraint, it would no longer be unique.". I also understand that Oracle<>SQL server, but is Oracle really dumb 😉 by allowing more than 1 null value for a unique constraint?
No, Oracle is Oracle. I would guess they treat each null as a unique value since null = null is not true.
I only worked with Oracle for a year as a developer and really did not get very deep into due to a lack of training opportunities.
April 2, 2012 at 3:57 pm
Here's a good discussion on Oracle's handling of NULL within unique indexes that pretty much says it all from every angle and perspective. Some posters even have DB2, MySQL and SQL Server references thrown in for good measure.
To summarize, in Oracle, in single-column unique indexes NULLs are completely ignored. In multi-column unique indexes NULLs are considered if one or more of the columns participating in the index are non-NULL, but when all columns are NULL the entry is ignored.
Thread: null values unique key
I am not sure what kind of discussions Oracle engineers had prior to arriving at a place where this behavior should be the default, but it is not intuitive coming from a SQL Server background, and the SQL standards arguably leave a bit too much to the imagination.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 2, 2012 at 4:24 pm
opc.three (4/2/2012)
To summarize, in Oracle, in single-column unique indexes NULLs are completely ignored. In multi-column unique indexes NULLs are considered if one or more of the columns participating in the index are non-NULL, but when all columns are NULL the entry is ignored.
I personally like this mechanism, and have used indexed views in the 'background' as uniqueness constraints for this type of setup, using the where clause to strip out nulls avoiding their consideration. I understand where they're coming from on that, but it's simply a different choice of implementation and both styles are equally useful, just under different circumstances.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 2, 2012 at 4:47 pm
Evil Kraig F (4/2/2012)
opc.three (4/2/2012)
To summarize, in Oracle, in single-column unique indexes NULLs are completely ignored. In multi-column unique indexes NULLs are considered if one or more of the columns participating in the index are non-NULL, but when all columns are NULL the entry is ignored.I personally like this mechanism, and have used indexed views in the 'background' as uniqueness constraints for this type of setup, using the where clause to strip out nulls avoiding their consideration. I understand where they're coming from on that, but it's simply a different choice of implementation and both styles are equally useful, just under different circumstances.
I can definitely see value in it working that way. Admittedly it bent my brain a little after only having a SQL Server lens to view things through. Someone on the thread said DB2 allowed you to configure unique index behavior which is an interesting idea, but with flexibility usually comes complexity, and that inevitably ends up adding overhead in some form. I often hear about how Microsoft engineers opt to forego adding knobs and dials like that to keep things from getting out of hand.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply