Create a composite foreign key in Ms Access

Question

I want to create a foreign key over two fields with the visual editor.

TabA: (idA, idB) <- idB is the primary key, (idA,idB) is unique

TabB: (id, idA, idB) <- id is primary key, (idA,idB) has an index not null

I can connect both tables in the visual editor. But when I set on the referential integrity, I get the error: "There is no unique index for the referenced field in the primary table" (translated from german).

Where is the problem?

Edit:

I finally found out that I was using the editor in the wrong way. My fault, that I start draging the fields from the wrong table. If I do right, everything works as expected.

I am not sure, if I will delete the question.

Can you post a quick screenshot of those indexes so I can verify you have indeed created a unique composite index? And a screenshot of the error you're getting? — Erik A

Gustav Gustav · Accepted Answer · 2018-08-27T11:01:22

TabA: (idA, idB) <- idB is the primary key, (idA,idB) is unique

But that won't work. Either use idB as the foreign key in the child table, or assign (idA, idB) as the primary key in TabA (bad idea).

By the way, when defining RI, Access will automatically create the unique index for the foreign key on the child table. It will, however, be hidden. But you don't have create it manually.

Create a composite foreign key in Ms Access

1 Answers