-geoff
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 table). ----- Original Message ---- From: Knut Anders Hatlen <[EMAIL PROTECTED]> To: Derby Discussion <[email protected]> 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 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
