[thelist] SQL join tables

[John Pye]

I don't understand the requirement for that primary key column, Lez.

What I would watch out for that you had a UNIQUE constraint on the composite
column. I use Oracle, and that would be done with something like

ALTER TABLE jointable
    ADD CONSTRAINT unq_jointable_row
        UNIQUE (tableAid, tableBid);

Then make sure the tableAid and tableBid columns are indexed for fast
searching.

Perhaps this is a databased specific question, I'm not sure.


John Pye
http://www.creativebase.com/
+44 (0)20 7258 6900


-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of Les Lytollis
Sent: Friday, June 29, 2001 5:27 PM
To: 'thelist at lists.evolt.org'
Subject: [thelist] SQL join tables


Hi,

I have taken ownership of a small internal project to provide a sort of
developers' knowledge base. This allows our staff to post articles or code
snippets under a number of different subjects.

A number of the tables in the database have join tables to eliminate
many-to-many relationships. The problem is that I have always given those
join tables their own ID field to provide a primary key for the table. The
tables in the current database use the two foreign keys to provide a
"composite primary key". Is there any benefit to adding the ID field? I have
a feeling that the composite key will cause problems.

An example -  if articles can belong to many subjects:

Article (ArticleID(PK),  title, body, author)
Subject (SubjectID(PK), description)
article_subject(articleID(FK), subjectID(FK))
 - I would add an article_subjectID to this table


TIA
Lez




---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !