If I am not mistaken, under ANSI SQL standards a UNIQUE constraint must be able to disallow duplicate non-NULL values but accept multiple NULLs.
Is there a way to achieve that with SQL Anywhere 11 without using triggers?
FYI, this is how SQL Server 2008 does it:
In my table below, either
asked 31 Mar '10, 17:51
There are two different mechanisms for this in SQL Anywhere: UNIQUE constraints and unique indexes. Indexing is not addressed by the ISO/ANSI SQL Standard.
In SQL Anywhere, a unique index over nullable columns treats each NULL value as a distinct value. So if one has
then no uniqueness violations will be reported.
Conversely, in SQL Anywhere a UNIQUE constraint does not permit null values at all.
for the above table will yield an error because the definition of column x permits null values.
For completeness, I note that in SQL Anywhere 12, CREATE INDEX supports the ability to treat NULL values as identical values, so that the index can contain at most one NULL value in that column. This functionality is provided through the WITH NULLS NOT DISTINCT clause.
answered 31 Mar '10, 19:13