0

In Microsoft Access I have created two tables. For the first table, Access has created a primary key ID field that does not allow duplicates. For the second table, Access has created a primary key ID field that does allow duplicates. I am trying to create a one to many relationship with the first table on the one side and the second table on the many side. But in the Edit Relationship screen, even after I click the Enforce Referential Integrity box, joining on the two primary key ID fields, Access has selected a one to one relationship and is not giving me the option to change that to a one to many relationship. What am I doing wrong?

1 Answer 1

1

For the second table, Access has created a primary key ID field that does allow duplicates.

A primary key, by definition, cannot have duplicates. Knowing this fact, MS Access probably assumes a join by two unique fields is guaranteed to be a one-to-one relationship.

What am I doing wrong?

Normally with proper relational form, in a one-to-many relationship, the primary key of the parent table is stored in a separate field from the primary key of the child table, known as the foreign key field instead. Foreign key fields are allowed to contain duplicates. The primary key of the child table should not be part of this relationship.

So you must add a separate field to your second table to be the foreign key field that relates back to the primary key field of the first table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.