--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> >Let's say I have two columns:
> >COL1 VARCHAR (35) CHARACTER SET ISO8859_1 COLLATE DE_DE
> >COL2 VARCHAR (35) CHARACTER SET ISO8859_1 COLLATE DE_DE
> >
> >Creating an index over those two columns will fail, because
> >the length of COL1 plus length of COL2 exeeds the maxium length
> >for an index.
>
> Oh, right - you hadn't mentioned that a COLLATE was involved.
> Why would you need a character set and a collate for an
> alphanumeric key??? If they are just numerals, use character set
> OCTETS for them.
>
> >This is what happend when I tried it. It works,
> >when COL1 and COL2 is of type VARCHAR(30).
>
> Yes, but you wouldn't get the four-column index for the unique
> constraint - it would be 120, which is about 40 bytes too big for
> collate de_de.

Oh, you are right. I don't need the COLLATE. But it will work if
I use only the ISO8859_1 character set (without COLLATE). Only
numerics will not be enough, because I wan't to make the PK a little
more readable (like 'abc_1234567890'). The ISO8859_1 character set
is used in the whole db, so I don't want to change it for the pk's.

> >
> >A primary key which consists of COL1 and COL2 described above.
>
> OK, you have some strong indications in this system NOT to use
> VARCHAR keys; or, if you do, to use the right character set.

As described above I will use VARCHAR. Mixing character sets in
a db will not cause problems, I guess. So which characterset is
used best for keys like 'abc_1234567890'?

Jürgen