db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: any feedback on this?
Date Tue, 24 Jun 2008 16:34:22 GMT
Alan Burlison <Alan.Burlison@Sun.COM> writes:

>>> Derby 10.4 allows multiple nulls for unique columns.
>>
>> I'm using 10.4.1.3 but it doesn't seem to work - is there some sort
>> of spacial chant I need to do to enable this?
>
> Ah, you have to add it as a 'unique' table constraint, if you add a
> unique index it doesn't work.  Odd, shouldn't you be able to do it
> either way?

The issue here is probably that Derby has supported CREATE UNIQUE INDEX
for a while, and the old semantics were preserved. When the unique
constraint was implemented, it was decided that it should follow the SQL
standard, which says that multiple nulls should be allowed.

I think the rationale for not allowing duplicate nulls in unique indices
was that 'index' was a store-level concept, not an SQL concept (CREATE
INDEX is not part of the SQL standard, as far as I know). In fact, the
UNIQUE constraints were implemented on top of non-unique indices.

I didn't follow the discussion about the implementation of the unique
constraint that closely, though, so anyone please correct me if I'm
wrong.

-- 
Knut Anders

Mime
View raw message