INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.

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

Run this check:

Select Type From Costs
Where Type Not In (Select Distinct Type From Modules)

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.

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


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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close