db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Unique constraint, or something like it
Date Tue, 13 Mar 2012 07:30:16 GMT
John English <john.foreign@gmail.com> 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

Mime
View raw message