Question about table relationships

[marf]

I have 2 tables
1. Called TblProjects (key: ProjID autonumber). It contains ProjectName,
ProjectNumber, ProjectStartDate, ProjectEndDate, etc

2. TblProjectIssues (key: ProjIssueID autonumber). It contains ProjectNumber
(as above), ProjectIssueDesc, ProjIssueStart, ProjIssueEnd.

A Project could have several issues, so the relationship needs to be
one-to-many.
When I try to establish a relationship between ProjectNumber on TblProjects
to ProjNumber on TblProjectIssues (I checked off Enforce Referential
Integrity). I get the following error: "No unique index found for the
referenced field of the primary table"

help

 

[BruceM]

Since you already have an autonumber primary key (PK), link to that field
rather than to ProjectNumber. If you wish, you can go to table design view
and set the ProjectNumber Indexed property to "Yes (no duplicates)". That
should allow you to create the relationship. However, the usual practice is
either to use ProjID as the foreign key field in tblProjectIssues, or to use
ProjectNumber as the PK in tblProjects. The advantage of an autonumber PK
is that the ProjectNumber could change (letter prefix, or whatever) without
disrupting the relationship.
If you are certain the ProjectNumber syntax will not change, you can use
ProjectNumber as the PK. If it is an incrementing number, or combination of
letter and number, you may be able to assign the number automatically,
depending on the business rule that determines the number. More details
available, if you like.