lutteroth89@... wrote:

>
> CREATE TABLE test
> ( Id VARCHAR(16) CHARACTER SET OCTETS NOT NULL,
> PRIMARY KEY (Id) );
>
> COMMIT;
>
> INSERT INTO test VALUES (ASCII_CHAR(1));
> INSERT INTO test VALUES (ASCII_CHAR(1) || ASCII_CHAR(0));
>
> The second INSERT gives me a:
>
> Statement failed, SQLCODE = -803
> violation of PRIMARY or UNIQUE KEY constraint "INTEG_2" on table "TEST"
>

Unfortunately, Firebird is almost certainly doing what it considers
intelligent key pruning and removes the trailing zeros in character
set OCTETS as not significant. You'll also have trouble if you
include a OCTETS field in a compound key because the algorithm for
concatenating keys first truncates insignificant characters then
pads the key with 0x0 bytes to a length that is a multiple of five(?)
bytes. Thus 0x1 0x0 0x1 will appear the same as 0x1 0x0 0x1 0x0

At least that would have been the case when I last understood
Firebird indexes which was a few years ago.


Good luck,

Ann