"Tony Toews [MVP]" wrote in message
...
"Neil" wrote:

However, I just came across some code in which the person created a
junction
table with a separate PK consisting of an autonumber field, and then the
two
fields.

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all
tables have
an autonumber primary key. It's also slightly easier to delete the
record in code.


Only an idiot would have a rule for no particularly good reason. Only an
imbecile would follow such a rule. A strong argument can be made for using
autonumber primary keys--especially if the target DBMS doesn't support FOR
EACH ROW triggers--but to just blythely add them for no particularly good
reason is a recipe for disaster. A clear understanding of how and when they
can be used and why is critical or you run the risk of a corrupt database.

Now if I was to have a child table from the junction table then I would
absolutely
use a autonumber primary key for ease of use when designing queries, forms
and
reports.

The theorists will argue. I don't care.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/