unique and null

[Ralf Wiebicke <ra...@exedio.com>]

> 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.

Re: unique and null

[Ralf Wiebicke <ra...@exedio.com>]

Hi Jose!

Thanks for the hint. I already noticed your posting while googleing the 
problem. However, this is not really useful for me. I'm maintaining a 
persistence framework and want(ed) to add support for derby. All those 
exceptions have to be implemented in the database-specific code of the 
framework. For this effort derby is just not (yet) important enough.

Best regards,
Ralf.




-- 
Ralf Wiebicke
Software Engineer

exedio GmbH
Am Fiebig 14
01561 Thiendorf
Deutschland

Telefon +49 (35248) 80-118
Fax +49 (35248) 80-199
ralf.wiebicke@exedio.com
www.exedio.com

Re: unique and null

[Jose de Castro <jo...@voxeo.com>]

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
jose.decastro@voxeo.com

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.
>