UNIQUE constraint

Question

UNIQUE constraint

Viewing 15 posts - 16 through 30 (of 64 total)

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

dawryn SSCertifiable Points: 5481 More actions · Answer 1

SQLRNNR (12/20/2012)
I don't disagree with the correct answer. I disagree with the explanation. I also do disagree with many of the complaints thus far about the question. You can most certainly have multiple null values in a unique constraint. Don't constrain yourselves to a narrow scope of a constraint on a single column - where you can have only one null value.
ALTER TABLE sometest
ADD CONSTRAINT someconstraint UNIQUE (testid,col1,col2);

Disagree!

This constraint can not represent value of Null even if all columns are populated with Nulls.

Besides, only one occurrence of Nulls in all columns is allowed.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

dawryn (12/20/2012)
SQLRNNR (12/20/2012)
I don't disagree with the correct answer. I disagree with the explanation. I also do disagree with many of the complaints thus far about the question. You can most certainly have multiple null values in a unique constraint. Don't constrain yourselves to a narrow scope of a constraint on a single column - where you can have only one null value.
...
Besides, only one occurrence of Nulls in all fields is allowed.

True only one occurence of null in all fields is allowed. But that is not the question. The question was simply if multiple nulls could be inserted.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

Gopi S

Ten Centuries

Points: 1091

December 20, 2012 at 12:50 am

#1570431

So far Jason is following me 😛

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Gopi S (12/20/2012)
Technically there is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same.
Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index also creates index that are physical structure that maintain uniqueness. It is a convenient way to enforce a Unique Constraint for SQL Server.
In above case "Unique Constraint" allows one "NULL".

Technically, there's a difference. Logically yes, they achieve the same, but technically they are different. An index is not a constraint and vice versa.

I know the first hit on Google is the blog post of Pinal Dave who says they are the same, but if you look a bit further on the search results, you'll also find posts that contradict this. By the way, to avoid copyright infringement or plagiarism, you should include a reference to the blog of Pinal Dave, not just copy paste its contents and pretend it's yours.

Difference Between Unique Index vs Unique Constraint[/url]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Gopi S (12/20/2012)
So far Jason is following me 😛

Yes, by saying your explanation is insufficient...

edit: I forgot to mention: thanks for the effort to create and submit a question. Hopefully you keep this up and hopefully there won't be any confusion on your next question

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Koen Verbeeck (12/20/2012)
Gopi S (12/20/2012)
Technically there is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same.
Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index also creates index that are physical structure that maintain uniqueness. It is a convenient way to enforce a Unique Constraint for SQL Server.
In above case "Unique Constraint" allows one "NULL".
...
By the way, to avoid copyright infringement or plagiarism, you should include a reference to the blog of Pinal Dave, not just copy paste it's contents and pretend it's yours.
Difference Between Unique Index vs Unique Constraint[/url]

+100:-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Koen Verbeeck (12/20/2012)
Gopi S (12/20/2012)
So far Jason is following me 😛
Yes, by saying your explanation is insufficient...

True dat!

I would like to see a better explanation that is more on-point with the question. It leaves less wiggle room for a melee.:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dawryn SSCertifiable Points: 5481 More actions · Answer 8

SQLRNNR (12/20/2012)
dawryn (12/20/2012)
SQLRNNR (12/20/2012)
I don't disagree with the correct answer. I disagree with the explanation. I also do disagree with many of the complaints thus far about the question. You can most certainly have multiple null values in a unique constraint. Don't constrain yourselves to a narrow scope of a constraint on a single column - where you can have only one null value.
...
Besides, only one occurrence of Nulls in all fields is allowed.
...The question was simply if multiple nulls could be inserted.

True. As I see it the Q was about inserting multiple nulls in a constraint, not multiple nulls in a part of constraint.

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

Thanks for remaining to place Reference. There is nothing to hide or pretend and i agree i followed some blog content and msdn. I know its best practice to put Ref link for more information. But i missed to place the reference link accidently.

The forum is not to argue or prove our technical efficiency. This is all about sharing our knowledge is positive sense. What we are learning is important than how we are learing.

dawryn SSCertifiable Points: 5481 More actions · Answer 10

BTW found this request for unique constraint ANSI compliance regarding multiple null values

Sean Pearce SSCoach Points: 15999 More actions · Answer 11

This is what Books Online says:

Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. By doing this the objective of the index will be clear.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 12

While I agree that the wording of the question is debatable (I chose my reply, and then was hoping I had second-guess the author's intention right), I also want to add that I am very happy to see a question on a useful feature such as filtered indexes. Not everyone knows about this feature and the ability it poses to enforce uniqueness on a subset of the data only.

I am also happy to see that someone already posted a link to the connect item on making the UNIQUE constraint ANSI-compliant. For those who didn't follow that link: the ANSI standard says that constraints should not apply to NULL values. So a UNIQUE constraint should, by default, allow as many NULL values as you want (because the NULL rows should not be checked).

The Microsoft implementation of the UNIQUE constraint is non-standard, and therefor dangerous - people with experience in other DMBS'es who switch to SQL Server can easily run into problems over it. And the reverse is true as well - a SQL Server expert who switches to another technology can also introduce errors if he thinks that the UNIQUE constraint will work the way he's used to.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Toreador SSChampion Points: 11365 More actions · Answer 13

SQLRNNR (12/20/2012)
...
This demonstrates that multiple null values is possible and that multiple null values is even possible within each of the columns so long as all of the columns together remain unique.

+1

I assumed that was what the questioner meant, and hence got it right for the wrong reasons.

Toreador SSChampion Points: 11365 More actions · Answer 14

Hugo Kornelis (12/20/2012)
The Microsoft implementation of the UNIQUE constraint is non-standard, and therefor dangerous - people with experience in other DMBS'es who switch to SQL Server can easily run into problems over it.

As someone brought up on Oracle, I wasted a day of my life learning that lesson 🙁

Yggaz SSChasing Mays Points: 635 More actions · Answer 15

SQLRNNR (12/20/2012)
I don't disagree with the correct answer. I disagree with the explanation. I also do disagree with many of the complaints thus far about the question. You can most certainly have multiple null values in a unique constraint.

ANSI NULL is not equal to any other value, even to another NULL. This is kind of broken in SQL Server - NULL always equals NULL when the unique constraint is considered. So the whole idea of the null-friendly unique constraint is to make the server behaviour more standard. We cannot have duplicates, but multiple NULLs are not duplicates as they are NOT equal to each other. So the idea should be "any number of null values is allowed, but not-null duplicates are prohibited".

And that is why the question is misleading. Yes we can prohibit any duplicates and allow any number of NULLs - but NOT with the constraint. And we could do that BEFORE 2008 as well - now we just got one more option.

Your "solution" allows multiple nulls (not any number of nulls, just more than one-two-three-whatever), but it allows duplicates on every column as well - as long as the whole set of values is not duplicated. Well if that is possible why bother to create unique constraint just on that column at all? Let's include the key, and the constraint will alllow any number of NULLs to be inserted in the column!