My earlier mail was with reference to primary keys/unique indexes.

ij> create table t4(id int, col1 varchar(6, primary key(col1));
ERROR 42X01: Syntax error: Encountered "," at line 1, column 39.
ij> create table t4(id int, col1 varchar(6), primary key(col1));
0 rows inserted/updated/deleted
ij> insert into t4 values(1,'t');
1 row inserted/updated/deleted
ij> insert into t4 values(1,'t ');
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by 'S
QL060503050258840' defined on 'T4'.
ij> insert into t4 values(1,' t');
1 row inserted/updated/deleted
ij> insert into t4 values(1,'  t');
1 row inserted/updated/deleted
ij> select '-'||col1||'-' from t4;
1
--------
-  t-
- t-
-t-

3 rows selected

-Rajesh

On 5/3/06, Rajesh Kartha <kartha02@gmail.com> wrote:
Steve Bosman wrote:

> Hi,
> The application I'm writing is copying records from an Oracle database
> to a Derby database and today I have been getting error 23505 (showing
> a primary key constraint violation) when two records have a key value
> differing by a trailing space, e.g. one record has the value 'treat 3'
> in one of its key fields and the other record has the value 'treat 3 '
> in the same key field. Can anyone tell me if this the correct Derby
> behaviour and I'll have to learn to live with it of if this is a bug.
>
> thanks
>
> Steve


As I understand, Derby preserves leading spaces but trims trailing ones. So
I  think what you are observing is expected.

Example;
ij> select *  from t1;
ID         |COL1
------------------
1          |te 3
1          | te 3
1          |  te 3


Regards,
Rajesh