db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: How should trailing spaces be treated in key fields
Date Thu, 04 May 2006 08:52:24 GMT
>>>>>>>>>>>> Stanley Bradbury wrote (2006-05-03 16:21:08):
> 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
> Hi Steve
> This seems like a bug to me.  

This is not a bug. According to the SQL standard comparision of two
strings of unequal length is done by padding the shortest string
before comparision. Thus, since, PRIMARY KEY is a UNIQUE NOT NULL
contsraint and UNIQUE is defined by comparision, this is strictly
according to the SQL standard (2003). I can provide the pointers to
the relevant chapters in the standard if needed.

You should rather file a bug against Oracle.

Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

View raw message