Referential Integrity Issue (2000)

Topic

New Reply

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

Reply | Quote

Replies

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:

• Make InvoiceNo the primary key in tblBilling, or set at least a unique key on it.
• 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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks Charlotte!!

Leesha

Reply | Quote