Referential integrity issue: no unique index found for the referenced field of the primary table

[cortextual]

I'm working with a database that was designed by someone else. I think he made a mistake setting up some tables and relationships, and I would really appreciate any suggestions on how to fix them!

I have two tables, called Modules and Costs. The Modules table should be the parent and it has the primary key ID, Module, and Type. The Costs table has the primary key ID, a foreign key CompanyID, Subscription, Type, and Cost. Under relationships two tables are connected by Module to Subscription and by Type to Type. When I try to enforce referential integrity I get the message "no unique index found for the referenced field of the primary table."

I think the issue is that in the Modules table there are a few modules with the same name, but they have different types. That would have been fine if he'd linked the table through the Modules primary key (put a ModuleID field into the Costs table) but he didn't. I see two options:

- best case scenario would be if I could add the ModuleID foreign key field to the Costs table now, but there are 1600 records in the table so I would need them to autofill correctly (by referencing both the subscription name and type) and I don't know how to do that/ if it's possible.

- alternatively I'm thinking that if I modify the module names in the Modules table to be unique then manually change them in the Costs table I'll then be able to enforce referential integrity. Given the number of records I'd like to avoid this option unless I can be 100% sure it'll work.

Any ideas? I'm a fairly new access user so I wouldn't be surprised if there's something obvious I missed, but I haven't found anything helpful online. The end result I'm looking for is to enforce referential integrity and cascade updates between the tables so if in the future a module name has to be changed it will also change that subscription name in the Costs table.

 

[Andrzejek]

Run this check:
[tt]
Select Type From Costs
Where Type Not In (Select Distinct Type From Modules)
[/tt]
If you have any outcome, you have some Types in Costs table that are not in Modules table.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

If you want to link module to subscription and type to type this is called a composite key. The combination of module and type in the table modules needs to be unique. You can have duplicates of each one seperately. If you wanted to make that combination a composite key, in the design view highlight both fields and pick the key symbol. This will enforce a unique index requiring the combination to be unique. If however you have multiple records with the same combination of these two fields in the modules table you will not be able to create that key after the fact. Only once you have a PK composed of these two fields will you be able to enforce referential integrity on those fields. It appears to me that they never created a composite primary key and then linked the tables by these two fields in the relationship window basically doing nothing. However the existing PK is the field "ID". Is this value a foreign key anywhere else in the database.

I hate composite keys and you could fix this pretty easy.
In your table cost add the field "moduleID_FK" and make it a number field.
Then run this query

UPDATE [Module] INNER JOIN Costs ON (Module.Type = Costs.Type) AND (Module.Module = Costs.Subscription) SET Costs.ModuleID_FK = [Module].[ID];

Now you have a new foreign key and you could delete subscription and type from the costs field. Since ID is a PK field already in the module table you can now link
Module.ID to moduleID_FK and enforce referential integrity.