Tony Toews [MVP] wrote:
"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.

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

Why introduce an autonumber as a PK when you don't need to? This
design is broken as noted in the discussion on library book example.
Why make the application code work harder than it has to? Let the DBMS
do the work that it is good at.

Note I'm no theorist. I'm a stuck in the trenches Software Engineer.
There are times to use an autonumber PK. But in this case I would say
you are fooling yourself in thinking this is good design practice.

Ed