Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

UNIQUE constraint Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 11:05 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, May 2, 2014 4:11 PM
Points: 645, Visits: 377
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
Post #1399072
Posted Thursday, December 20, 2012 5:02 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:40 PM
Points: 8,743, Visits: 9,292
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
Post #1399135
Posted Thursday, December 20, 2012 5:12 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:40 PM
Points: 8,743, Visits: 9,292
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

On those pointes I definitely agree!


Tom
Post #1399137
Posted Friday, December 21, 2012 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #1399296
Posted Friday, December 21, 2012 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:13 AM
Points: 42, Visits: 46
+1
Post #1399394
Posted Friday, December 21, 2012 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:13 AM
Points: 42, Visits: 46
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!
Post #1399397
Posted Monday, December 24, 2012 5:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #1400003
Posted Thursday, December 27, 2012 9:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.
Post #1400658
Posted Thursday, December 27, 2012 12:16 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.

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.
Post #1400724
Posted Thursday, December 27, 2012 12:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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.

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.

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
Post #1400727
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse