Thanks, Michael. You are correct, Derby, like DB2, finesses this issue
by not allowing nullable columns in unique constraints. I have closed
this bug.
Cheers,
-Rick
Michael J. Segel 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.
