The posted code does not provide a workaround as it does not allow duplicate rows with nulls:
INSERT INTO foo VALUES (6, NULL); INSERT INTO foo VALUES (6, NULL); <-- this fails Error: org.apache.derby.client.am.SqlException: 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 'FIDX' defined on 'FOO'., SQL State: 23505, Error Code: -1 Dan --- "Michael J. Segel" <[EMAIL PROTECTED]> wrote: > On Wednesday 26 October 2005 19:22, Rick Hillegas > wrote: > > Sorry to top post... > > Sigh. > Seems that some are quick to pull the trigger and > call everything they see a > bug! > > This is not a bug. ;-) > Its a design issue. > > I'm sure that this distinction is going to be lost > on a couple of people, and > it goes back to an earlier isssue about how each > database handles > constraints. You can always e-mail me directly and > take this offline. > > Whomever designed how Derby handles constraints, > Derby does not allow for > NULLs in columns that have been identified in a > constraint. Its actually in > the reference manual. (The exercise of finding it is > left to the > student.... ;-) > > > Now if you want a simple work around, just create a > unique index on the table. > Here's the code I just ran and it works: > > CREATE TABLE foo > ( id int NOT NULL, > name char(25) > ); > > CREATE UNIQUE INDEX fidx ON foo (id, name); > > Then I added the following rows: > INSERT INTO foo VALUES (1,'AAA'); > INSERT INTO foo VALUES (2,'BBB'); > INSERT INTO foo VALUES (3,'CCC'); > INSERT INTO foo VALUES (4,'DDD'); > INSERT INTO foo VALUES (5,'EEE'); > INSERT INTO foo VALUES (1,'FFF'); > INSERT INTO foo VALUES (6, NULL); > > INSERT INTO foo VALUES (1,'AAA'); -- THIS ROW > FAILS! > SELECT * FROM foo; > > NOTE THE FOLLOWING: > > Indexes are not the same as Constraints, however > they can be used to achieve > the same goal. > > Please remember, because you may not get the results > that you expect, that > doesn't mean that you actually have a bug. > > If someone wants to consider how to redesign how > contraints work, you have a > couple of other considerations. For example, which > takes precedence? SQL > statements that manage the container, or SQL > statements that manage the data? > (And that's a loaded question.... ;-) > > HTH > > -Mikey > > PS. Again, what do I know? Rumor has it my older > siblings dropped me on my > head when I was an infant. ;-) > > > Hi Dan, > > > > I believe that the Sybase behavior is correct. I > have logged bug 653 to > > track this issue. > > > > Regards, > > -Rick > > > > Dan Meany wrote: > > >I noticed that in Derby a unique constraint on > two > > >columns A and B, with B nullable, will prevent > > >inserting two identical records that contain NULL > in > > >B. > > > > > >This is different from some other databases such > as > > >Sybase that do allow it (I assume where the null > > >records are not in stored as part of the unique > > >index). > > > > > >I noticed this while transfering data from Sybase > to > > >Derby using Apache dbutils and getting rejected > > >duplicate records. > > -- > Michael Segel > Principal > MSCC > (312) 952-8175 >
