John English <[email protected]> writes: >> Derby also has unique indexes that, in contrast to unique constraints, >> consider two null values duplicates: >> >> CREATE UNIQUE INDEX FOO_3 ON foo(section,title) > > But that won't actually prevent me entering duplicate values with a null > section value, will it?
I think it should. ij> CREATE TABLE foo (section INTEGER, title VARCHAR(100)); 0 rows inserted/updated/deleted ij> CREATE UNIQUE INDEX FOO_3 ON foo(section, title); 0 rows inserted/updated/deleted ij> INSERT INTO foo values (null, 'abc'), (null, 'abc'); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'FOO_3' defined on 'FOO'. This asymmetry between unique constraints and unique indexes is a bit confusing. The reason for the asymmetry is that CREATE UNIQUE INDEX was supported a long time before support for the unique constraint on nullable columns was added, and its semantics were preserved for backwards compatibility. Also, CREATE UNIQUE INDEX isn't specified by the SQL standard, so it doesn't need to follow SQL's definition of uniqueness, whereas the unique constraint is clearly specified in the standard. -- Knut Anders
