Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Referential Integrity Issue (2000)

    When I go to set up a relationship between InvoiceNoReference in tblPaymentAllocations with InvoiceNo in tblBilling, I get an error message "No unique index found for the referenced field of the primary field." Both tables have primary keys set to fields called Autonumber. I don't understand the issue.
    I've posted the database with only the tables in it.

    Thanks!
    Leesha
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referential Integrity Issue (2000)

    If you want to create a one-to-many relationship between two tables, you create a link between a field on the "one" side (the primary key) and a field on the "many" side (the foreign key). There must be a unique index (usually the primary index) on the field on the "one" side.

    For example, in the relationship between tblDemographics and tblBilling, AccountID is the primary key in tblDemographics (the "one" side) and AccountID is the foreign key in tblBilling. There is a unique index, in fact the primary index, on AccountID in tblDemographics.

    There is no unique key on InvoiceNo in tblBilling or on InvoiceNoRefernce in tblPaymentAllocations, so Access cannot determine what kind of relationship it is. If you double click the line, you will see that Relationship Type says Indeterminate. Since neither of the fields is unique in its table, Access can't decide what to do if a value is modified/added/removed, so referential integrity cannot be enforced.
    So you should do one of the following:
    <UL><LI>Make InvoiceNo the primary key in tblBilling, or set at least a unique key on it.
    <LI>Join tblBilling to tblPaymentAllocations on the current primary key AutoNumber (so InvoiceNoRefernce would be linked to AutoNumber).[/list]To read up on this, type "define relationship" in the Answer Wizard.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referential Integrity Issue (2000)

    Hi Hans,


    >>Make InvoiceNo the primary key in tblBilling, or set at least a unique key on it.

    How do I put a "unique Key" on it? I can't find anything on unique keys only primary keys. Do I do this in the table itself under indexing??

    >>To read up on this, type "define relationship" in the Answer Wizard.

    I have read this prior to posting. My understanding was that in setting the primary key on autonumber that a unique index for the table had been set.

    Thanks Hans,
    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referential Integrity Issue (2000)

    You create a unique key by going into the table design. For a single field unique key, just set the indexed property of the field to Yes and No Duplicates.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referential Integrity Issue (2000)

    Thanks Charlotte!!

    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •