Problem with setting Referential Integrity in Access 2000 1

[achau]

I have tried to enforce referential integrity between two data tables, and I got the following error message when I clicked on the "create" button in the Edit Relationship:

"No unique index found for the reference field of the primary table."

The relatioship type was recognised by Access as "One-to-many".

I have already set the primary keys in the two tables, with Indexed="Yes (no duplicates)" in the parent table.

Any help appreciated. Thanks.

Al

 

[Craig0201]

Al,

A little Access glitch this one if I remember rightly.

Click on the icon for the indexes and in the primary key index, a field name will appear on two lines. Delete one and all should be OK.

Craig

 

[achau]

Thanks Craig! Using your method, Access changes the indexed part from "Yes" to "No"?!?!

The problem seems to lie in one of the fields in the parent table as I am trying to link two fields common to the two tables. If I just link one of them it seems to work, but it doesn't like linking two at the same time. I wonder if it could be something to do with the fact that one field has been specified as indexed = "Yes (Duplicates OK)" and the other as indexed "Yes (No Duplicates)".

Al

 

[JAXDBase]

This is correct referential integrity. RDBMS does not allow you to link a child table that is related to a parent with duplicate primary keys. The solution would be to normalize the table by either setting key dups = no or to add an additional key that will allow you to make each row distince using the primary key.