1:M relationship

Hi,
I am trying to estblish the relationship between tbles and I get the following errors:


no unique index found for the referenced field of primary table


my desing of tables contains of course primary keys but because i have also foreing keys I am making them PK in the tables where they should be ( 1:M) as well in the Database Design Mode. is it why I am having this problem? I tried to put only one PK in each table and once I do the relationship it works fine ( well it looks 1:m ) but all i am worried about is the outcome, i am not sure if i will get the desired result, please can you correct me if i am wrong in this?
thanks

Hi Salim,
it seems you have more than one primary key per table, if that's the case then it could be the source of your problems. That is unless the primary keys are compound PK which are basically a combination of at least two fields. See if you can stick with one PK per table and lets hear how it goes.

Hi Slyce,
thanks for your reply, do you mean that if I keep the FK ( Forgein Key )not as PK ( Primary Key) would be fine? I tried it and it seems okay, but as I said I am not quite sure about the desired outcome for my queries. So far it's okay, tried it with couple fields and see how it goes all along, again my concerns are that if a FK is assigned in another table then it's not necessary that it must be a PK

Hey Salim,
if i understand you correctly; you need to have your foreign keys(in child fields) relating to primary keys in (parent fields) that's the basis of the one to many relationships. It may be a good sign that "so far it's okay". My understanding is that a foreign key should be a PK in the table with parent fields.

So why is it then giving me the error message ? if a FK must be PK in the parent field, then it should be okay.
I think you understand my point and all i am getting is the error, so what do you think is going wrong?
here is my table for example:


Booking_tbl
Booking_ID (PK)
Customer_ID (PK) ( but is a foreign key because of the result of 1:M)

Vehicle_tble
Vehicle_ID (PK)
booking_ID (FK) ( but PK key in the table this is my understanding of how to design it in access)


now I am wrong because I get the error message?
If I don't set booking_ID as PK then nothing happens.
much appreciated

Hey Salim,
the table structure you sent shows you have two primary keys for the Booking_tbl, now the only way you can get away with that is if you have a composite primary key (a combination primary key composed of the two fields). If that's not what you have then it's most likey the source of your problem. You can have only one PK per table.What seems right would be to leave Boking_ID as PK in Booking_tbl, remove the PK on Customer_ID in Booking_tbl.

Hi Slyce
I think you're right about it, I tried it many times and didn't work, now it seems working, I just wanted to double check to avoid any undesired result. Thanks for your tips and help.

You are most wellcome, glad my tips helped.