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 12345»»»

Unique constraint on a nullable column Expand / Collapse
Author
Message
Posted Wednesday, December 1, 2010 10:43 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, December 18, 2014 3:37 PM
Points: 199, Visits: 265
Comments posted to this topic are about the item Unique constraint on a nullable column
Post #1029073
Posted Thursday, December 2, 2010 12:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 4,158, Visits: 5,556
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
Posted Thursday, December 2, 2010 12:54 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:49 PM
Points: 1,484, Visits: 1,967
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
Posted Thursday, December 2, 2010 1:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:49 AM
Points: 2,620, Visits: 2,467
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
Posted Thursday, December 2, 2010 1:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Posted Thursday, December 2, 2010 2:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:17 AM
Points: 1,608, 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>)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
Posted Thursday, December 2, 2010 2:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:49 AM
Points: 2,620, Visits: 2,467
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
Posted Thursday, December 2, 2010 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:36 PM
Points: 6,133, Visits: 8,398
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
Posted Thursday, December 2, 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, November 19, 2014 1:29 AM
Points: 3,074, Visits: 115
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
Posted Thursday, December 2, 2010 5:25 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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 12345»»»

Permissions Expand / Collapse