Share via

No unique index found for the referenced field of the primary table

Anonymous
2014-10-07T13:23:30+00:00

Howdy Folks,

I am attempting to build relationships between multiple tables. I have indexed and ordered each table in the same way. Each table has a PK autonumbered field.

However these aren't the fields I am trying to create the relationship between. The fields (Climate ID) I am trying to create the relationships between are not PK's, they are indexed as "Yes (Duplicates OK), and the data type is short text and contains alphanumeric data. When I attempt to create the relationship and enforce referential integrity I get the "No unique index found for the referenced field of the primary table"

Hope someone can steer me in the right direction

Cheers

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

12 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2014-10-07T14:04:18+00:00

    If you want to enforce referential integrity for a relationship, there must be a unique index (for example the primary key, but it can also be another index, as long as it is unique) on the field(s) on one side of the relationship. If there is no such index, Access cannot enforce referential integrity.

    100+ people found this answer helpful.
    0 comments
  2. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2014-10-07T13:58:09+00:00

    The field in the table on the One-side of the relation must have a unique index. A duplicate index is not good enough.

    This is most likely a gap in your understanding of relational database design. Please give us a succinct description of the tables in question and their most important fields and data types.

    20+ people found this answer helpful.
    0 comments
  3. Anonymous
    2014-10-07T15:12:03+00:00

    I think the gap in my understanding of relational database design is obvious. It is definitely an area I am struggling with. In terms of the tables in in question, I have a table, "SampleDate" with 12 fields that I want to join to 27 other tables named "element1", "element2", "element3" etc that contain weather data.

    The SampleDate table contain the following fields:

    • ID, which is autonumbered, and indexed as "Yes (No Duplicates)"
    • Climate ID, which as stated before is not a primary key, structured as "short text" data type and contains alphanumeric data, and indexed as "Yes (Duplicates OK)". Climate ID's represent a weather station and may be repeated because there are weather readings for each date (i.e. weather readings from each day of a month for one weather station)
    • Date, which is not a primary key, and structured, structured as "Date/Time" data type and displayed as long date, and indexed as "Yes (Duplicates OK)". Dates may be repeated as there can be readings from several weather stations on any given day.

    Each of the weather tables contain the following fields:

    • ID, which is autonumbered, and indexed as "Yes (No Duplicates)
    • Climate ID, which as stated before is not a primary key, structured as "short text" data type and contains alphanumeric data, and indexed as "Yes (Duplicates OK)".
    • Date, which is not a primary key, and structured, structured as "Date/Time" data type and displayed as long date, and indexed as "Yes (Duplicates OK)"
    • weather parameter such as "Air Temp" (element 1), "Mean Wind Speed" (element 2), "Total Snowfall" (element 3) etc

    What I am needing to do is join my master list of dates "SampleDates" to each element and draw out the pertinent weather data as it relates to climate ID and date.

    As stated in my earlier post, when I attempt to build relationships to do this, I keep getting the message

    "No unique index found for the referenced field of the primary table"

    10 people found this answer helpful.
    0 comments
  4. HansV 462.6K Reputation points MVP Volunteer Moderator
    2014-10-07T15:30:10+00:00

    Is the combination of Climate ID and Date unique in the SampleDate(s) table? If so, create a unique index on the combination of these two fields:

    Note that the second row in the Index Name column is blank. This makes MyIndex consist of the combination of Climate ID and Date.

    As you see, MyIndex is a unique index, but not the primary key.

    There doesn't need to be such an index in element1 etc.

    When creating the relationship, start by dragging Climate ID to Climate ID. But then also select the Date field in both tables in the Edit Relationship dialog:

    8 people found this answer helpful.
    0 comments
  5. Anonymous
    2014-10-08T15:37:07+00:00

    Thank you for the response. You are correct. ID in the SampleDate table is not related to the ID in each element table. As well, the combination of Climate ID and Date is not unique. I have cleared the "Enforce Referential Integrity" and started creating relationships. What I have been doing is using outer joins from the main SampleDate table to each element table. I end up having 26 joins as there are 26 element tables with varying data in terms of dates, and climate ID's (not all records have the same number of records and differ in dates or climate ID's). Can anyone tell me if there are any other ways of making these joins/building relationships more efficiently? 

    Cheers

    If you have a separate table for each element that strongly suggests to me that your design is wrong on the whole. I think taking the time to understand relational database structure would make things a whole lot easier for you. There is a free eBook by Crystal titled "Access Basics" at my website here

    3 people found this answer helpful.
    0 comments