You might have missed my previous post regarding null unique columns:
12/12/2006 2:45 PM : Jose de Castro wrote:
I found a way around this issues while converting my application from
MySQL to Derby. It appears that Derby will allow nullable columns in
unique constraints if defined as in a separate statement as the 'CREATE
TABLE'. I have tested this functionality and believe that is functions
as per the SQL specification.
For example, the following statement fails with a message of 'ERROR
42831: 'B_ID' cannot be a column of a primary key or unique key because
it can contain null values':
CREATE TABLE a(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
b_id bigint default 0,
PRIMARY KEY (id),
UNIQUE (b_id)
);
However, if you create the index in a separate statement is works just
fine:
CREATE TABLE a(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
b_id bigint default 0,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX AUniqueBID ON a(b_id);
Hope this helps,
Jose de Castro
Sr. Software Engineer
Voxeo Corporation
[EMAIL PROTECTED]
Ralf Wiebicke wrote:
In the meantime, even though these differences can prove frustrating, I
hope you will keep your eye on the product and provide additional
feedback on the features you consider important but lacking in Derby.
Derby does not allow null in unique columns. This time I give it up. May be I
try again later that year.
Ralf.
