Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Marianna Petrovich » Unique constraint on a nullable column
Add to briefcase
44 posts, Page 1 of 5
12345»»»

Unique constraint on a nullable column Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
BowieRules!
Posted Wednesday, December 01, 2010 10:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 28, 2013 4:22 PM
Points: 198, Visits: 227
Comments posted to this topic are about the item Unique constraint on a nullable column
Post #1029073
Stewart "Arturius" Campbell
Posted Thursday, December 02, 2010 12:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 3,129, Visits: 4,312
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”
Post #1029088
tommyh
Posted Thursday, December 02, 2010 12:54 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943
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
Post #1029094
Carlo Romagnano
Posted Thursday, December 02, 2010 1:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:56 AM
Points: 1,972, Visits: 1,822
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.
Post #1029098
Christian Buettner-167247
Posted Thursday, December 02, 2010 1:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:12 AM
Points: 2,526, Visits: 3,620
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
Post #1029099
Dietmar Weickert
Posted Thursday, December 02, 2010 2:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, January 11, 2013 8:20 AM
Points: 1,608, Visits: 373
Adding a WHERE clause is no option for two reasons:

1) It only works for INSERT...SELECT...WHERE... statements. If INSERT...(<field list>)VALUES(<value list>) 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...


Best regards,
Dietmar Weickert.
Post #1029118
Carlo Romagnano
Posted Thursday, December 02, 2010 2:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:56 AM
Points: 1,972, Visits: 1,822
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.
Post #1029120
Hugo Kornelis
Posted Thursday, December 02, 2010 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 5,244, Visits: 7,059
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
Post #1029141
Rodrigo Siqueira de Souza
Posted Thursday, December 02, 2010 5:17 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:04 AM
Points: 3,074, Visits: 96
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
Post #1029168
Ninja's_RGR'us
Posted Thursday, December 02, 2010 5:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 21,357, Visits: 9,539
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.
Post #1029171
« Prev Topic | Next Topic »

Add to briefcase
44 posts, Page 1 of 5
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.