UNIQUE constraint

Question

Post reply

UNIQUE constraint

Viewing 15 posts - 31 through 45 (of 64 total)

You must be logged in to reply to this topic. Login to reply

Gopi S Ten Centuries Points: 1091 More actions · Answer 1

Gopi S

Ten Centuries

Points: 1091

December 20, 2012 at 2:19 am

#1570457

udayroy15 Ten Centuries Points: 1076 More actions · Answer 2

Me really confused when I saw the answer, the question should be "Unique Index" instead of "Unique Constraint".

Gopi S Ten Centuries Points: 1091 More actions · Answer 3

Here is the proof that i followed placing reference link without pretending as if mine.

http://www.sqlservercentral.com/questions/T-SQL/71798/

DugyC Hall of Fame Points: 3804 More actions · Answer 4

dineshbabus (12/19/2012)
Today's question is completely misleading. Keeping unique constraint in mind which allows only one NULL value i answered worngly.

+1

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

rhythm.varshney Ten Centuries Points: 1138 More actions · Answer 5

Gopi S (12/20/2012)

Here is the proof that i followed placing reference link without pretending as if mine.

http://www.sqlservercentral.com/questions/T-SQL/71798/%5B/quote%5D

I guess posted the wrong link 🙂

Mike Hays SSCommitted Points: 1871 More actions · Answer 6

As with the previous posts, I too got it wrong & disappointed with the wording of the question. I understood a UNIQUE CONSTRAINT to be a type of filter on a table to ensure data integrity, nothing to do with an index.

In MSDN definition of a "UNIQUE CONSTRAINT" from:

http://msdn.microsoft.com/en-us/library/ms191166%28v=sql.105%29.aspx

MSDN states:

"Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column."

The concept is a good and raises a good debate. Maybe SQL Server will meet ANSI standards SQL:92, SQL:1999, and SQL:2003 for a UNIQUE CONSTRAINT in the next version.

Dave62 SSCertifiable Points: 7459 More actions · Answer 7

Thanks for the question!

Lucky for me I answered before having my coffee. After less than 5 seconds of thought I chose the correct answer because I have used filtered indexes and know they have been available from 2008 and up.

Coffee and more thought would have caused me to notice the unique constraint wording and totally messed up my ability to choose the right answer. :hehe:

Enjoy!

Ken SSCoach Points: 17191 More actions · Answer 8

I too looked at this as specifically dealing with the unique constraint syntax on a single column instead of unique indexes. Based on the sample code, the author had unique indexes in mind and should have labeled the question as so. Good discussion points here though!

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 9

While I'm not thrilled with the wording in the question, I took it to mean that the constraint held multiple null values which isn't a confusion anyone else has pointed out yet, I do like what the question was getting at. This is a good way to to deal with the problem.

TriggerMan Say Hey Kid Points: 672 More actions · Answer 10

These questions really are like individuals' SQL scripting styles... there are always 50 perspectives... I give you from BOL from SQL 2008 Express R2 (which many of you Gurus constantly lament us Noobs for not using):

"unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column."

...and the original question:

"Is it possible to create a table with unique constraint that allows multiple NULL values from SQL Server 2008 onwards? "

I understand that there are nuances in the wording between the question and reference. I implore those posting questions: PLEASE state ALL of the variables when posting the question. The fact that it is also a T-SQL question worth ONE point leads one to believe that there are no tricks or esoteric musings.

Missing one pointers is very discouraging to us NOOBS.. I think the key to keeping your sanity with SQL is to pick your favorite guru and emulate his/her style. It doesn't matter whom you choose, there will be 100 Million DBAs/users who will tell you that, "...sure you got the desired results, but THIS(MY) way is more efficient." I've never seen ones and zeroes be interpreted so subjectively. I guess that's the beauty of taming the SQL beast. Value-added IT support. 🙂

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 11

I knew there might be a controversy over this question but I took a stab at YES because of the Itsik Ben-Gan article I read on this subject. Still I was nervous that the wording would bite me because I usually go the wrong way.;-)

http://www.sqlmag.com/article/sql-server/unique-constraint-with-multiple-nulls

Terry300577 Hall of Fame Points: 3391 More actions · Answer 12

TriggerMan (12/20/2012)
These questions really are like individuals' SQL scripting styles... there are always 50 perspectives... I give you from BOL from SQL 2008 Express R2 (which many of you Gurus constantly lament us Noobs for not using):
"unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column."
...and the original question:
"Is it possible to create a table with unique constraint that allows multiple NULL values from SQL Server 2008 onwards? "
I understand that there are nuances in the wording between the question and reference. I implore those posting questions: PLEASE state ALL of the variables when posting the question. The fact that it is also a T-SQL question worth ONE point leads one to believe that there are no tricks or esoteric musings.
Missing one pointers is very discouraging to us NOOBS.. I think the key to keeping your sanity with SQL is to pick your favorite guru and emulate his/her style. It doesn't matter whom you choose, there will be 100 Million DBAs/users who will tell you that, "...sure you got the desired results, but THIS(MY) way is more efficient." I've never seen ones and zeroes be interpreted so subjectively. I guess that's the beauty of taming the SQL beast. Value-added IT support. 🙂

+1

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

Good question about a frequently unknown topic. I guessed wrong because I couldn't decided the slant the author was using. I figured if I said it was possible because of filtered indexes I would be wrong because the question clearly states constraint...but then maybe it was just an attempt at showing how a unique constraint only allows a single null. In the end I chose the wrong path. The horse has been beat to death, the wording was pretty suspect and misleading but the topic is a good one. I look forward to Gopi's next question. Keep them coming.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TomThomson SSC Guru Points: 104773 More actions · Answer 14

Pretty poor QotD today.

The explanation is complete rubbish - nothing at all to do with unique constraints.

The answer is nevertheless correct, as Jason already pointed out so I don't need to roll out the code snippet I keep handy to prove that the statement about NULL and unique indexes in BoL is sloppily worded and misleading.

I got it wrong because I made two assumptions. My first assumption was that the question meant what it said (unique constraint, not unique index). Clearly, given the explanation, that was an incorrect assumption - which demonstrates that the wording of the question was sloppy, to say the least.

The second assumption was that the author of the question would believe the nonsense statement in BoL that any column in a unique constraint can have only one null, so picking that answer would work; dilly me. I don't know whether that assumption was right or wrong, because the author apparently believed that a unique filtered index was a table constraint, but the assumption cost me a point.

Tom

sknox SSChampion Points: 12388 More actions · Answer 15

Yggaz (12/19/2012)
The problem lies in the words "unique constraint". It has at least TWO meanings: "something that does not allow duplicates" and "SQL Server object implemented via unique index".

This is the inherent problem: in English (and particularly in IT), the same terminology is often used when describing a concept and a specific implementation of the concept. We had a similar issue on a QotD a while back regarding foreign keys.

I usually try to clarify by referring to the concept in all lower case, while referring to the named implementation in proper case. So in this case, while it's possible to create a unique constraint [concept] that supports multiple NULLs using a Unique Index [specific implementation] in SQL Server, it's not possible to do so with a Unique Constraint [specific implementation].

But even so, those I'm communicating with have to know my syntax, because there's not a universal elegant way to make the distinction in this language.