Re: Many to many

From: Joe Celko <71062.1056_at_compuserve.com>
Date: Wed, 13 Dec 2000 19:07:33 GMT
Message-ID: <918hdc$2e6$1_at_nnrp1.deja.com>

>> 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? If they are, what considerations to make when to chose one way or another? <<

This is complicated, so let's actually write code!!

CREATE Table Boys
(boy_id INTEGER NOT NULL PRIMARY KEY,
 name CHAR(20) NOT NULL);

CREATE Table Girls
(girl_id INTEGER NOT NULL PRIMARY KEY,
 name CHAR(20) NOT NULL);

CREATE TABLE Orgies
(boy_id INTEGER NOT NULL

        REFERENCES Boys(boy_id)  <== foreign key
        ON DELETE CASCADE
        ON UPDATE CASCADE,
 girl_id INTEGER NOT NULL
        REFERENCES Girls(girl_id)   <== foreign key
        ON DELETE CASCADE
        ON UPDATE CASCADE,

 PRIMARY KEY (boy_id, girl_id)); <== compound primary key

Allows these rows:
('Joe Celko','Ann Margret')
('Joe Celko', 'Christy Brinkley')
('Joe Celko', 'Brooke Shields')
('Bruce Willis', 'Brooke Shields')
('Tom Cruise', 'Brooke Shields')

however, this schema is a little different:

CREATE TABLE Playgirls
(boy_id INTEGER NOT NULL PRIMARY KEY <== primary key

        REFERENCES Boys(boy_id)  <== foreign key
        ON DELETE CASCADE
        ON UPDATE CASCADE,
 girl_id INTEGER NOT NULL
        REFERENCES Girls(girl_id)   <== foreign key
        ON DELETE CASCADE
        ON UPDATE CASCADE;

allows only
('Joe Celko', 'Brooke Shields')
('Bruce Willis', 'Brooke Shields')
('Tom Cruise', 'Brooke Shields')

CREATE TABLE Playboys
(boy_id INTEGER NOT NULL

        REFERENCES Boys(boy_id)  <== foreign key
        ON DELETE CASCADE
        ON UPDATE CASCADE,
 girl_id INTEGER NOT NULL PRIMARY KEY   <== primary key
        REFERENCES Girls(girl_id)   <== foreign key
        ON DELETE CASCADE
        ON UPDATE CASCADE;

allows only
('Joe Celko','Ann Margret')
('Joe Celko', 'Christy Brinkley')
('Joe Celko', 'Brooke Shields')

CREATE TABLE Couples
(boy_id INTEGER NOT NULL UNIQUE <== candidate key

        REFERENCES Boys(boy_id)  <== foreign key
        ON DELETE CASCADE
        ON UPDATE CASCADE,
 girl_id INTEGER NOT NULL UNIQUE <== candidate key
        REFERENCES Girls(girl_id)   <== foreign key
        ON DELETE CASCADE
        ON UPDATE CASCADE,

 PRIMARY KEY (boy_id, girl_id)); <== compound primary key

Allows these rows:
('Joe Celko', 'Brooke Shields')
('Bruce Willis', 'Demi Moore')
('Bill Clinton', 'Monica Lewinsky')

You can try other combinations of overlapping keys, but you might want to look at the ORM modeling methods for help. www.InConcept.com is a good place to start.

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com
http://www.deja.com/ Received on Wed Dec 13 2000 - 20:07:33 CET