db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Geoff hendrey <geoff_hend...@yahoo.com>
Subject Re: any feedback on this?
Date Wed, 25 Jun 2008 14:52:02 GMT


I am very glad that 10.4 allows multiple nulls for unique columns. 
On 10.3 the lack of this feature made it difficult to add a unique column to a table that
was already populated with data, since the existing rows would all have null for the new column,
which was not allowed.(kind of catch-22 as far as adding a new unique column to an existing

----- Original Message ----
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
To: Derby Discussion <derby-user@db.apache.org>
Sent: Tuesday, June 24, 2008 9:34:22 AM
Subject: Re: any feedback on this?

Alan Burlison <Alan.BurlisonI @Sun.COM> writes:

>>> Derby 10.4 allows multiple nulls for unique columns.
>> I'm using 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

Knut Anders

View raw message