December 1, 2010 at 10:43 pm
Comments posted to this topic are about the item Unique constraint on a nullable column
December 2, 2010 at 12:24 am
This was removed by the editor as SPAM
December 2, 2010 at 12:54 am
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
December 2, 2010 at 1:22 am
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.
December 2, 2010 at 1:22 am
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
December 2, 2010 at 2:33 am
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.
December 2, 2010 at 2:40 am
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.
December 2, 2010 at 3:42 am
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!
December 2, 2010 at 5:17 am
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
December 2, 2010 at 5:25 am
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.
December 2, 2010 at 5:38 am
Truly a good question because it provoked a lot of thought as to which techniques are possible, valid and practical.
I got it "right" (yay!), but after reading the discussion I think the "adding a where clause in ALL inserts" is very dubious, simply because all inserts may not have a where clause. It could be done if there were no INSERT or UPDATE permissions granted on the table and all access is through stored procedures.
December 2, 2010 at 6:28 am
If you have a unique constraint on a column, it doesn't matter what you do with triggers, WHERE clauses and so on - you can still only have one NULL. I think your "correct" answers should have included the word "instead", for the avoidance of doubt.
John
December 2, 2010 at 6:33 am
The only worth of the question is the good news:
"filtered index" (SQL Server 2008).
This feature is present in MS-ACCESS starting from version 1.0 (year 1990).
December 2, 2010 at 6:39 am
Very interesting information, and I appreciate it. Way too many answers for this early in the morning though 🙂 Is that 1/256 statistically?
December 2, 2010 at 6:45 am
pjdiller (12/2/2010)
Very interesting information, and I appreciate it. Way too many answers for this early in the morning though 🙂 Is that 1/256 statistically?
Yup, but without factoring for the unknown number of correct answers.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply