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.

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.

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.

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