Problem enforcing referential integrity

[RaunLGoode]

I have inherited an Access 2007 database with two tables called “Incidents” and “CorrectiveActions”.
tblIncidents
IncidentID (Auto Numbered Primary Key)
IncidentNumber
Etc.
tblCorrectiveActions
IncidentNumber(Primary Key)
CorrectiveAction(PrimaryKey)
Etc.

There can me more than one CorrectiveAction for each IncidentNumber. (a one to Many relationship)

I want to enforce referential integrity between these two tables with a one to many relationship.
When I try to use the relationship editor I get an error message stating “No unique index found for the referenced field
of the primary table.” If I add the CorrectiveActionNumber to the CorrectiveActions side of the relationship editor, I get a notification that I haven’t picked a matching field on the “Incidents” side of the editor. There is no such field in the “Incidents” table.

Obviously I am inexperienced in Access. What am I doing wrong and how do I enforce referential integrity between these two tables with a one to many relationship? This DB is also accessed via an ASP app, so I would like to avoid adding additional fields etc to the tables if possible.

 

[boblarson]

What you are finding is how it can be difficult when using a composite key (which is in tblCorrectiveActions). That is one reason why I will use an Autonumber surrogate as primary key and then set a multi-field index with no duplicates specified on the two fields so it can't be duplicated.

 

[RaunLGoode]

This all came about because I am looking for a way to enable cascading deletions. Would a possible work-around be creating a deletion query for all records in the "child records"(all CorrectiveActionNumbers in tblCorrectiveActions matching the IncidentNumber) , then deleting the the parent record? Would Access allow me to delete a record in the parent folder if no child records exist?

 

[boblarson]

Yes, if no child records exist you can delete the parent. If the relationships are not set correctly then it will allow you to delete the parent even if the child records do exist.