At 05:00 PM 9/11/2004 +0000, you wrote:

>I'm trying to resolve this case without duplicating attributes on
>indices. I have:
>
>TableA
>id_A long (PK)
>...
>
>TableB
>id_B long (PK)
>...
>
>and
>
>TableAB
>id_A long (FK)
>id_B long (FK)
>...
>
>I need a PK for TableAB, but if i use both, id_A and id_B for such
>purpose, I end with an index with contains duplicate attributes, which
>is very inconvenient (at least for inserting and planning).
>For duplicate attributes, i mean, they're already on other index.
>As far as i'm concerned, i cannot build an index with both attributes
>that can be used for PK and FK purposes (do I ? ) .

You can have multiple indexes on the same columns. The caveat (from the
optimisation POV) is to avoid having more than one index with identical
attributes.

That's not the problem here. The PK will have a unique index across id_A
and id_B; id_A will have a (non-unique) index as a FK to TableA..id_A and
id_B will have a (non unique) index as a FK to TableB.id_B.

>Also a check constraint uses an index (AFAIK).

No.

>Is there a new id for the N to N table the only (clean) way out here ?

It is clean way out that I would always want to use if the keys were
complex. But, with your simple keys, it's not necessary.

./heLen