SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UNIQUE constraint


UNIQUE constraint

Author
Message
Narud
Narud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1641 Visits: 507
Good question, poor title.

=> If you ask about constraints I think in constraints.

=> If you ask about indexes I think in indexes.

But if you ask about vacations I think that it's time to take on :-P
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14240 Visits: 12197
George M Parker (12/20/2012)
Totally disagree with the answer to the question.

"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 trouble with that quotation from BoL (you should attribute what you quote, but never mind) is that it's actually wrong (like a few other things in BoL). You can demonstrate it's wrongness using the code that SQLRNNR posted earlier, or with the code snippet I use to educate people on this topic, which is
if exists (select * from sys.objects where type = 'U' and name = 'k') drop table k
create table k (a int primary key,b int, c int, unique(b,c))
insert k values (0,0,0),(1, 1, null), (2,null,1), (3,2,null), (4,null,2),(5,3,3),(6,null,null)
select * from sys.key_constraints where name like 'UQ__k__%'
select * from k order by a
drop table k



Tom

TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14240 Visits: 12197
Narud (12/20/2012)
Good question, poor title.

The question is explicitly about constraints:
Is it possible to create a table with unique constraint ....

and the title is too. So why is the question good if the title is poor?
Anyway, there's nothing wrong with the question or indeed with the answer (the answer is correct), it's just that the explanation (not the answer) is about indexes, not about constraints.

=> If you ask about constraints I think in constraints.

=> If you ask about indexes I think in indexes.

But if you ask about vacations I think that it's time to take on :-P

On those pointes I definitely agree!

Tom

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10916 Visits: 11980
L' Eomot Inversé (12/20/2012)

I greatly wish that a large number of people would pile in and downvote that awful connect item. As Michael Lato pointed 5 and a quarter years ago (and as was confirmed by MS nearly 5 years ago), there's no need to change because it's possible to use a filtered index to have the effect Hugo was asking for. As Erland Sommarskog pointed out 5 years ago, the current MS definition of a UNIQUE constraint is extremely useful in many situations. I added a very tongue in the cheek comment early last year suggesting a move in very much the opposite direction - get rid of the idiocy in the standards - because I thought the intransigence and apparently brainless ISO-worship of supporters of the connect item in the face of there being perfectly satisfactory means of achieving the sorts of constraints that they want without destroying the current extremely useful feature of SQL server needed an example of similar stupidity (the no nulls at all unique constraint is trivial to achieve, just as is the mutiple nulls allowed one, and therefor throwing away the existing useful feature to make it the only sort of unique constraint would be exactly as stupid as implementing this connect item) to wake them up to their own stupidity. Unfortunately that didn't work. In fact I suspect it encouraged David Portas to add another [url=https://connect.microsoft.com/SQLServer/feedback/details/658638/dont-allow-nullable-columns-as-part-of-unique-constraints]dangerous connect item a few days later, which I would also encourage everyone to vote against (if they have time - it's rather unlikely to happen anyway); I tried sarcasm there too - again, it didn't work.


Hi Tom,

I don't know what happened when you wrote that - I have never seen that kind of language from you before! "intransigence" (had to look that word up in a dictionary) ... "apparently brainless" ... "stupidity" ... not your style at all! Luckily, you targeted the up-voters of that connect item, not the submitter, so I don't need to feel offended. ;-)

My point in submitting that Connect suggestion is not that you cannot implement the business rule of uniqueness for non-missing values in any other way. You can. Just as you can implement the business rule of uniqueness for non-missing value with the added restriction of only a single missing value (plus the additional really awkward wording for UNIQUE on multiple columns). We do not even need a UNIQUE constraint at all, there are workarounds for everything.
My point is also not that I want the current implementation of UNIQUE removed from the product.

My point is that IF a feature is implemented that has the same name as a feature in the ANSI standard, then it should behave exactly as described in that standard. If a vendor chooses to offer additional options as well - fine, but use optional keywords for them, and make sure that the defaults are for ANSI behaviour.
Good examples of this are TOP (non-standard - but no problem, leave it out and your queries behave as described in ANSI, put them in and you explicitly choose to get non-standard behaviour - and you'll get a very clear error message when porting that code to another platform); and DECLARE CURSOR (which can be used in two different ways, either ANSI-compliant with limited options, or non-standard with more options).
A terrible example is the UNIQUE constraint - declare it and at first sight, it behaves exactly as expected. In the worst case, you'll only realise that it behaves differently on your new platform when the application is already in production.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
TriggerMan
TriggerMan
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 104
+1
TriggerMan
TriggerMan
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 104
Terry300577 (12/20/2012)
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


I found my Guru! :-P
StarNamer
StarNamer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1638 Visits: 1992
As explained in several answers, the 'solution' created a UNIQUE INDEX (on the non-NULL parts of the table) not a UNIQUE CONSTRAINT on the entire table.

Derek
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 1046
Lots of posts and comments but none of them, including the answer code, shows a UNIQUE CONSTRAINT on a TABLE column that allows more than one NULL value.
A UNIQUE INDEX is not a UNIQUE CONSTAINT and they are used for two differant things.
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 1046
Hugo Kornelis (12/21/2012)
[quote/]My point is also not that I want the current implementation of UNIQUE removed from the product.

My point is that IF a feature is implemented that has the same name as a feature in the ANSI standard, then it should behave exactly as described in that standard. If a vendor chooses to offer additional options as well - fine, but use optional keywords for them, and make sure that the defaults are for ANSI behaviour.


Hugo you are better than this.
MS SQL Server is an ACID compliant Transactional RDMS system that Logs all DML.
It also supports ANSI SQL as a secondary language. w00t

So your arguments suggesting we should limit our Choices and options as DBA's and Developers when using MS SQL server so it will support some language standard that is secondary to T-SQL and ACID support seem very contrary and argumentative. Cool
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10916 Visits: 11980
SanDroid (12/27/2012)
Hugo Kornelis (12/21/2012)
[quote/]My point is also not that I want the current implementation of UNIQUE removed from the product.

My point is that IF a feature is implemented that has the same name as a feature in the ANSI standard, then it should behave exactly as described in that standard. If a vendor chooses to offer additional options as well - fine, but use optional keywords for them, and make sure that the defaults are for ANSI behaviour.


Hugo you are better than this.
MS SQL Server is an ACID compliant Transactional RDMS system that Logs all DML.
It also supports ANSI SQL as a secondary language. w00t

So your arguments suggesting we should limit our Choices and options as DBA's and Developers when using MS SQL server so it will support some language standard that is secondary to T-SQL and ACID support seem very contrary and argumentative. Cool

ANSI is a standardisation institute. ANSI has published several standards, over the years, for relational databases. Those standards include descriptions of language features. Companies that create a relational database can choose to adhere to the standard, either fully or partially, or not to adhere to it at all. As far as I know, there are no companies with 100% ANSI compliance, but most major players in the RDBMS market comply with a large part of the ANSI standard.

SQL Server supports only one language: T-SQL. That language complies for a good part with the ANSI standard. It doesn't implement all ANSI features, and it implements lots of features that are not in the ANSI standard.

I don't suggest limiting choices and options. I do think that it is needlessly confusing that SQL Server implements one specific feature that has the same name as an ANSI standard feature, but a different implementation.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search