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
