Marc Batchelor wrote:

>
> However, if my table definition changes to this:
>
> create table foo(
> col1 decimal(18, 0) not null,
> col2 varchar(189) not null
> );
>
> unsuccessful metadata update
> -key size exceeds implementation restriction for index "FOO1"

Oh dear. You've run into two of my least favorite problems. Compound
keys and checking key length at definition time.

Compound keys. Firebird does something quite tricky with compound keys
so it can do suffix compression inside a compound key. In your
particular case, it's a disaster, but in the more usual case, where both
keys are varchar and normally have lots of trailing blanks, it works well.

What it does is to first perform suffix compression, then pad each key
out to the nearest multiple of 4, and put a segment count in every fifth
byte. That works a treat on blanks at the end of key segments. As it
happens, you have a non-compressible first segment, so in your case the
compression just pads out the key. And in fact, it pads out the key
that combines an eight byte integer and a 189 byte character field to
255 bytes.

Originally, InterBase didn't have a limit on the size of keys you could
define. We assumed that if somebody defined a really big key,
compression would take care of the problem. Then one day it didn't, and
somebody got a key size error trying to insert a record. So now it
checks for the absolute worst case and gives an error if that would
overflow.

Regards,


Ann