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

> >Yes, I have. The problem is, that indices are limited concerning

the

> >length of values. In my db I have many tables which describe m:n
> >relations and firebird doesn't allow me to add a unique index over
> >two foreign keys, then.
>
> I don't get what you mean here: many:many? Could you explain why

Yes: m:n means many:many. Sorry, I thought 'm:n' is known in
the english language, too.

> 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. This is what happend when I tried it. It works,
when COL1 and COL2 is of type VARCHAR(30).

> >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.

Jürgen