No unique index found for the referenced field of the primary table

[Gavx]

Here is the table structure;

tblGuestProducts
ProductID PK Compound key, unique, Long integer
GuestID PK Compound key, unique, Long integer

tblGuestAccommodation
AccomID PK
GuestID FK Long Integer

I am trying to establish Referential Integrity between these two tables based on the GuestID but am receiving the message in the title.

Could the problem be that I am trying to use a compound key?

thanks for any help.

 

[arnelgp]

you cant, both tables should have same number of field (for pk and fk), for this to work, and the datatype on each field should be same on both table.

 

[plog]

To expound on arnel's advice: A foreign key in one table, must be the primary key in the other. The primary key in tblGuestProducts is a compound key, so a foreign key to it must use both fields of your primary key.

To fix this, I advise adding an autonumber primary key to tblGuestProducts (e.g. GuestProductID) and use that in tblGuestAccomodation.

 

[gemma-the-husky]

How is your set up working, in terms of rooms, guests, and products purchased.

I expect real world systems tend to allocate purchases to the room only (ie irrespective of the guest) , hence the importance of the guest holding a charge certificate as authorisation for room charges.

 

[jdraw]

To further expound on plog's advice, if you use a single autonumber as PK of a table (also called a surrogate primary key), you can create a unique composite index of the fields that you would have used for your compound PK. The unique composite index, which may be familiar to users, will prevent the insertion of duplicate records. In a sense, this surrogate/autonumber works for the database; the unique composite index works for the user ( a poor statement really).

The issue with compound Primary keys is when you have subsequent relationships with other tables. You have to include all of the parts of the compound PKs to make the new key and that can get quite unwieldy (as plog said).

But Dave asks an interesting question --real world strikes again.


Composite unique index

 

[Gavx]

Thanks for your replies. Up to now I always thought natural keys (that coincidentally can be compound) were ideal but now I see some drawbacks.

Your replies are unanimous in suggesting adding a surrogate key. However I think I will go the other way and create the compound index in the child table as these fields are already in place - I just have to tweak a few things. And in this way the table will be narrower-er.

Thanks again.

 

[gemma-the-husky]

the only real issues with natural keys are

a) compound keys are fiddlier to manipulate
b) non-numeric keys are slightly less efficient
c) changing a field in a PK requires cascading updates throughout the database - this is the big one. I would never use a PK (and therefore a FK) that might ever change

 

[Cronk]

In the OP's table, each Guest could only have one product.

But if we want to get into "real world" situations, purchases would be allocated to a booking, or at least a Room-Date because a different guest could be in the same room on another day (assuming the room is booked on a 24 hour basis).

Also the same guest could be in a different room during another trip, or even change rooms during the same trip.