Referential Intergrity on tables with compound key

[winshent]

I am trying to enforce referential integrity on tables where a
compound key is implemented.

I want to enforce integrity on the DETAIL table so that it can only
use an SOR_ID from the SOR table that has a corresponding PhaseID in
the HEADER table. Here is my current diagram:

http://i234.photobucket.com/albums/ee261/winshent/IT/Relationships2.jpg

For example, the records in the image below exist SOR. I want the
database to restrict a record with PhaseID=64001 and SOR_ID=10A200
being added to DETAIL as there is no such record in SOR.

http://i234.photobucket.com/albums/ee261/winshent/IT/SORQuery.jpg

The only solution i can think of at the moment is to build two
queries. One which concatenates SOR.PhaseID & SOR.SOR_ID, and another
which concatenates HEADER.PhaseID & DETAIL.SOR_ID... and then create a
relationship between the two queries.

 

[Jeff Boyce]

Another approach might be to keep the "compound" index, but not use it as a
composite key. Instead, a single Autonumber field could provide the
connection between tables, and a unique index on the compound fields could
ensure unique-ness.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP