db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rajesh Kartha" <karth...@gmail.com>
Subject Re: How should trailing spaces be treated in key fields
Date Thu, 04 May 2006 01:11:18 GMT
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
>

Mime
View raw message