AutoNumber issue

[Bigz132]

So I am taking a DB class at my school and we are supposed to resolve a M:M relationship with a third table. Easy enough I understand how to do that however the third table is supposed to have just the primary keys from each table and they are both auto numbers. This is creating issues because apparently a table can't have 2 autonumbers. How do i resolve this issue




Thanks for the help

Grant

[June7]

The third table fields would not be autonumber type, they would be number (integer) type and will store the values of the autonumber fields from the other 2 tables. This establishes an association between records of the two 'lookup' tables.

The third table can have an autonumber field but it serves no role in relating records of the other two tables.

[Bigz132]

So I just choose to not enforce integrity? Or can i enforce that and change the types? Thanks for the response. Huge project this weekend and small things like this keep taking up all my time.

[June7]

The types in third table must be changed to number, not autonumber.

Enforce relational integrity if you want.

However, I suggest you set the two fields as a compound index (not compound primary key) to prevent duplicate pairs.

[orange]

Watch this free video.

Regarding the junction table.
It can have its own autonumber PK (recommended)
Or it can have a compound PK made up of the keys of the other two tables. In the junction table these would be Number (Long Integer) data types.

If you go with a separate autonumber PK, then the keys of the other 2 tables would be combined to make a unique compound index. As June said, the unique compound index will prevent duplicate records.