Re: Many to many

From: Mark B. Wallace <mark.wallace_at_verizon.net>
Date: Wed, 06 Dec 2000 16:29:58 GMT
Message-ID: <3A2E6982.6AFCB9C4_at_verizon.net>


Arne Jonsson wrote:

> On 5 Dec 2000 04:10:34 GMT, hidders_at_REMOVE.THIS.win.tue.nl (Jan
> Hidders) wrote:
>
> >Arne Jonsson wrote:
> >> For about two years I´ve been living happily in the belief that if you
> >> have a many to many-relation you split it in a third table between the
> >> other two, where you make the primary keys of these two tables to a
> >> composite primary key in the third. Now, taking a course in
> >> SQL-server, I am taught that the two primary keys can be used as two
> >> foreign keys in the new table, or as one primary and one foreign key.
> >> This have made me confused. Are all the ways right?
> >
> >No. You cannot implement a many-to-many relationship with a primary key
> >and a foreign key. Suppose you have tables A and B with the primary
> >keys #a and #b. If you add #b as a foreign key to A then there will
> >always be at most one tuple in B associated with every tuple in B. So
> >that is not a many-to-many relationship.
> >
> >--
> > Jan Hidders
>
> Does it make any sense to keep the two keys as foreign keys in the
> third table, not making them to composite primary keys?
>
> Arne

It might. Assume two tables, ORDER and PART, that participate in a many-to-many relationship, LINE-ITEM. What should be the primary key of LINE-ITEM? The default choice, as you originally believed, is the composite key, (ORDER-NUMBER, PART-NUMBER). But, an equally valid choice would be the composite key (ORDER-NUMBER, RELATIVE-LINE-NUMBER-WITHIN-ORDER), where the latter column is introduced to track the relative position of the specific part request in the order, and also must be unique within any specific order. In that case, the original primary keys, ORDER-NUMBER and PART-NUMBER, would be implemented in the LINE-ITEM table as a (piece of a composite) primary key and a foreign key (that is not a piece of the primary key), respectively. I suspect that your instructor confused you, and perhaps some others who responded to your message, by not giving sufficient emphasis to the parenthetical phrases in my previous sentence.

It is also not inconceivable that there could be reasons to introduce a wholly new primary key, say ABSOLUTE-LINE-NUMBER, and leave both ORDER-NUMBER and PART-NUMBER as merely separate foreign keys within the LINE-ITEM table.

Mark Received on Wed Dec 06 2000 - 17:29:58 CET

Original text of this message