db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jose de Castro <jose.decas...@voxeo.com>
Subject Re: unique columns must be nullable ?
Date Tue, 12 Dec 2006 19:45:40 GMT
Xanana Gusmao wrote:
> On this page http://wiki.apache.org/db-derby/HibernateHelp
> it says:
>
> "Hibernate Annotations do not work because Derby does not allow a unique column
> to be nullable"
>
> Is this (that Derby's unique column must be non-nullable) still true ?
>
>
>   
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

Mime
View raw message