Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unique constraint on a nullable column


Unique constraint on a nullable column

Author
Message
BowieRules!
BowieRules!
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 283
Comments posted to this topic are about the item Unique constraint on a nullable column
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5769 Visits: 7130
Interesting question.

But the reason for wanting a unique column except where (one or other condition (e.g. NULL)) is true escapes me.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
tommyh
tommyh
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: 1560 Visits: 2000
There is no **** way that "By adding a where clause in ALL inserts that checks for the existence of new value in the table" is a fix to a problem with a constraint. Since it doesnt prevent adhoc inserts unless the developer knows that he MUST add extra code to his query. So this doesnt really fix anything.

/T
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3600 Visits: 3233
tommyh (12/2/2010)
There is no **** way that "By adding a where clause in ALL inserts that checks for the existence of new value in the table" is a fix to a problem with a constraint. Since it doesnt prevent adhoc inserts unless the developer knows that he MUST add extra code to his query. So this doesnt really fix anything.

/T

I agree with you. It's the DBMS that should guarantee data consistency and not the "good will" of the developer. So the option "By adding a where clause in ALL inserts that checks for the existence of new value in the table" is not a valid option.
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Hm, this is a very good topic, and most answers make sense.

But the last one with the where clause for all inserts is nonsense. You cannot "enforce" uniqueness by telling yourself to always use a where clause.

I was also caught by the calculated column answer. That is an interesting technique I had not considered, but which is indeed a viable solution.

Best Regards,

Chris Büttner
Dietmar Weickert
Dietmar Weickert
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: 1608 Visits: 374
Adding a WHERE clause is no option for two reasons:

1) It only works for INSERT...SELECT...WHERE... statements. If INSERT...(<field list>WinkVALUES(<value list>Wink is used, the WHERE clause is not available.
2) INSERT...SELECT...WHERE... may insert multiple values at once, all of which are not yet present in the table, so the WHERE clause will give you no restrictions. Still within the inserted values there may be duplicates which will be inserted later unless you enforce uniqueness by means of an index as well - and then the WHERE clause was pointless and creating nothing but overhead.

At least it is just a single point I lost... Sad

Best regards,
Dietmar Weickert.
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3600 Visits: 3233
INSERT ... WHERE ...
... and UPDATE ????
I could insert NULL and then change (UPDATE) it to an existing value!
The solution is insert/update by stored proc, but the risk to miss the right value is high.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11548
I have to agree with the others that adding a WHERE clause to all INSERT statements is not a good workaround, for the reasons already given (not robust, not possible for INSERT VALUES, not sufficient for multi-row insert, no protection in case of updates).

I do agree with the other four options.

It is interesting that the Microsoft interpretation of the UNIQUE constraint is not in accordance with the ANSI standard, that explicitly requires that NULL values are exempted from all constraints - so a UNIQUE constraint should not disallow multiple NULLs. And frankly, in cases where I needed a UNIQUE constraint on a nullable column, I almost always had to use one of these workarounds, which indicates that the ANSI standard is more suited for actual use.
I proposed a suggestion on Connect to gradually (to prevent breaking existing code) change the current implementation to an ANSI-standard implementation. Despite 99 votes in favor and only 4 votes against, MS has not implemented the suggested first step in SQL Server 2008 or in SQL Server 2008R2. I have now changed the proposed solution for a first step in SQL Server 2011. If you agree with me on this, then please make yourself heard, and vote for my suggestion at https://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values

Thanks in advance!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Rodrigo Siqueira de Souza
Rodrigo Siqueira de Souza
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3074 Visits: 120
Good question, but not very clear.

1) What about updates?

2) Is it possible to replace de unique constraint by a filtered index? For me, the unique constraint could not be droped ou altered.

3) All other complaints made on this discussion.

Regards

Rodrigo
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
97% wrong answers??? Maybe there's something not quite correct with the question.


More to the point. If my column needs to contain NULL, I'm not going to fiddle around and add a fake value and then have to worry about that issue everytime I query that table.
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