Share via

I keep getting an error code when trying to enforce referential integrity in a relationship

Anonymous
2022-01-23T08:54:46+00:00

I keep getting the error code "Data in the table ____ Violates the referential integrity rules." I have an existing relationship between my two tables already, all I want to do is enforce referential integrity between the current relationship. I have skimmed through many questions online and I can't quite seem to find what I'm needing.

Here is my database in question>> https://drive.google.com/file/d/1CcTpKPs3wRRLcjNwApTLVup9-Cx2kui0/view?usp=sharing

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

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-01-23T11:53:50+00:00

    It is as Scott said, you have a value being used in your Employee table that does not exist in your Department table. In this instance, it is a simply typo

    Aministration should be Administration

    Once you fix that, everything will work as it should.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2022-01-23T19:46:49+00:00

    Of course, it was a typo haha. Thanks so much!

    0 comments
  3. Anonymous
    2022-01-23T13:49:49+00:00

    Sorry, I didn't notice that you had provided a link to your database. But I see that another expert downloaded it and confirmed what I said about the missing data.

    0 comments
  4. Anonymous
    2022-01-23T11:58:59+00:00

    On a separate note, you aren't properly structuring your tables. Your tables should normally have a numeric autonumber primary key, not a textual one.

    WP_Department should be

    DepartmentNumber (pk - autonumber)

    Department

    ....

    and then your WP_Employee would reflect this

    EmployeeNumber (pk -autonumber)

    FirstName

    LastName

    DepartmentNumber (fk - number)

    ...

    Then you build your table relationship between DepartmentNumber on both tables.

    In your WP Employee Data Form then you would normally change the DepartmentNumber control to a combo box to simplify data entry.

    0 comments
  5. Anonymous
    2022-01-23T11:00:13+00:00

    Hi Jay, I will try to help.

    To enforce referential integrity, there must be a value in the many side for every value in the 1 side. The usual cause for this error is a missing value on the 1 side. To trace this you can do an unmatched query using the query wizard and search for values in the 1 side that are not in the many side.

    If you need further clarification on this feel free to ask.

    0 comments