Relation Many to Many and Junction Table

Question

User-1885804455 posted

Let's say I have two tables:

Table1:
Table1_ID (AutoNumber, Primary key)

Table2:
Table2_ID (AutoNumber, Primary key)

If I would create a junction table out of these two tables, then it also need to have two fields which both have primary keys as well, like this:

Table1_Table2_Junction:
Table1_ID (AutoNumber?, Primary key)
Table2_ID (AutoNumber?, Primary key)

But do they also need to have the datatype set to "AutoNumber" as well? Or could it just be "Number"?

Answer 1

User-84896714 posted

Hi Ptolemy,

But do they also need to have the datatype set to "AutoNumber" as well?

They can't set to "AutoNumber". Just set to "Number" as well. AutoNumber is a type of data used in Microsoft Access tables to generate an automatically incremented numeric counter. It can't be insert or update.  In your tables, the values of Table1_ID and Table2_ID are from Table1 and Table2.

Best Regards,
Li Wang

Answer 2

User-1885804455 posted

OK, I now realize that having it set to AutoNumber would ruin the whole concept of M:M relationship. So the point would be to be able to have repeated ID's in the Junction Table, either in the left or right field. This would be a problem if having boht field as AutoNumber, and they're already set to AutoNumber or other unique ID in their respective table.