table foreign key ambiguity (1 Viewer)

Hello

Please see attached diagram, and I am getting some ambiguity while migrating this tables to sql server, even though there are primary key is there still it is giving error explained in attached picture.

Please advise.

it is due to your multi field primary keys. Not tested but would only work if your relationship was based on all three fields. If you are doing this to prevent duplicates, better to just create a multi field index with no duplicates - your order details table already has an autonumber primary key so your 3 way primary key is irrelevant anyway since this will make each record unique in its own right anyway.

Recommend you make the seqnum field the sole primary key and see if this solves the problem.

Looking at the diagram, I can't tell which is the parent table.

I believe I might have told you in another thread that relationships are made betwee the primary key (that would be ALL fields if the PK is multi-field) and data fields in the child table.

Although it is not wrong to use multi-field primary keys, you will find joins and things like combo and listboxes to be much simpler when you just stick to autonumbers. CJ did mention creating a unique index. That is what I do if I have a business rule to implement and I don't want to use the compound natural key as the PK.

BTW - an autonumber is unique and should NEVER be part of a multi-field PK or unique index. If you have an autonumber in a table, it should ALWAYS be the primary key.