Share via

Microsoft can't create this relationship and enforce referential integrity error

Anonymous
2020-05-24T10:44:02+00:00

I followed the thread raised on this subject in the past but that didn't help me.  That thread is now closed.

I'm learning Access from an online tutorial with three very simple tables:-

There is no data in any of the tables but the relationship I'm trying to create between Customers and Orders will not allow "enforce referential integrity" although the other relationship between Orders and Products gets set up with no problem!  In Customers the CustomerId is AutoNumber, Primary key and in the Orders table it is Long Integer, Indexed, Duplicates OK.  Access version is 2016.

I've tried deleting and re-creating the Customers table, tried 'Compact & Repair Database' - no success!

What am I doing wrong please?

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2020-05-24T12:34:45+00:00

Thanks. There is already a record in the Orders table, with CustomerId = 0. This is not a valid CustomerId from the Customers table.

Either delete this record, or change the CustomerId to 1 (the ID of Ken Wheeler).

After that, you'll be able to enforce referential integrity for the relationship between Customers and Orders.

Tip: you don't really want Orders records with 0 in the CustomerId or ProductId field.

Open the Orders table in design view.

Select the CustomerId field and clear the Default Value property.

Do the same for the ProductId field.

Save the table design.

Was this answer helpful?

10+ people found this answer helpful.
0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-05-24T14:07:02+00:00

    Thank you @Scottgem for the offer of help - appreciated!   @HansV picked up the problem and has straightened me out 😉.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2020-05-24T12:44:35+00:00

    Hi Celt, I'm an independent advisor and will try to help

    What EXACT message are you getting when you try to establish referential integrity? You said you tried recreating the Customer table. Did you try recreating the Orders table? Also did you drag the CustomerID from the Orders table to the Customers table or vice versa?

    I'm sorry I don't have an answer for you yet, only questions, but that is often the case with Access questions. So please answer the questions and we'll see what we can do.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-05-24T11:36:26+00:00

    If you're willing to upload the database to OneDrive, Google Drive, Dropbox or similar, and post a link to the uploaded version in a reply, I and others will take a look at it.

    Thanks very much - uploaded to Google Drive - link:-

    https://drive.google.com/drive/folders/1KG\_GNcYPAoY0JjrKe0BZ6mvcrVS5ShDP?usp=sharing

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    2020-05-24T11:19:57+00:00

    If you're willing to upload the database to OneDrive, Google Drive, Dropbox or similar, and post a link to the uploaded version in a reply, I and others will take a look at it.

    Was this answer helpful?

    0 comments