At 02:33 PM 11/02/2004 +0000, you wrote:

> > you think Fb couldn't give you unique index over two foreign keys,
> > if you wanted it for an intersection table?
>
>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.

> > >So I think I will go back to the compound PK idea.
> > >Or don't you mind if you can't create an unique index for such a
> > >case.
> >
> > Do you mean, create a separate surrogate PK for the intersection
> > table and control the uniqueness of the foreign key pairs using
> > manual triggers, to avoid having the primary key index interfering
> > with the two foreign key indexes?
> > If so, I agree it's an issue you would want to test, from a
> > performance POV, and compare with the performance of the natural PK
> > compounded over the four columns.
>
>Yes. That was what I meant.
>
> >
> > >Remember, compound primary keys are not possible, either.
> >
> > I don't remember. Why?
>
>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.

/heLen