Microsoft Access - I get Error "No unique index found for the referenced field of the primary value"

Asked By Bill Fensterblatt on 02-Nov-12 08:12 AM
  1. Requirements (simplified for present purposes)
    1. Access 2010
    2. Employee table
      1. Primary key "First Name", "Last Name"
        1. Neither column by itself is guaranteed to be unique.
      2. Additional columns "City", "State" for the Location of the Employee.
    3. Location table
      1. Has primary key "City", "State"
      2. Neither column by itself is guaranteed to be unique.
      3. No other columns;
    4. Many-one relationship between "Employee" and "Location".
    5. Need to enforce referential integrity
    6. Creating meaningless autonumber fields and using them as single-column primary key, in lieu of the above multi-column keys, is not permitted.

  2. Steps to recreate the failure

    1. Go to Relationships window

    2. Go to "Edit Relationships"
    3. Create a many-one relationship between two tables
    4. Click "Edit Relationships/Enforce Referential Integrity"
    5. Get the error "No unique index found for the referenced field of the primary value"
  3. Questions:
    1. Am I doing something wrong?
    2. Is there any way to meet all of the requirements in Access 2010? 
    3. If it is not possible, is it correct to say that Access 2010 permits you to create a multi-column primary key, but if you do, then Access blocks you from applying referential integrity?
Pat Hartman replied to Bill Fensterblatt on 30-Dec-12 06:31 PM
This sounds like a school assignment because you wouldn't do this in a real application.  Based on the error message, the PK is not correctly defined.   To make a multi-field PK, use the ctl or shift keys in combination with click to select up to ten individual or adjacent columns.  Once all the required columns are highlighted, press the key button on the ribbon to define the PK.
FYI, you will find it easier in the long run to use autonumber primary keys.  When you do have a single or compound candidate key (potential unique primary key), create a unique index using those columns to enforce the business rule but let the autonumber be the PK.  This enforces the business rule of uniqueness but still allows you to use autonumber PKs.