No unique index found for the referenced field of the primary table. (Error 3609)

[Stapes]

Hi

I am trying to create a relationship, using Tools >> Relationships, &
keep getting this error message. I am trying to link table
TM_CampaignCustomers with TM_CompContact. TM_CampaignCustomers has as
its primary key field TM_CCID. TM_CompContact has PK_Contact. Bothe
are defined as Autonumber, Indexed, No Duplicates. The join is from
PK_Contact in TM_CompContact to FK_Contact in TM_CampaignCustomers.
FK_Contact is defined as indexed - duplicates OK.

When I tick the box 'Enforce Referential Integrity' in the Edit
Relationships dialogue - I get this error message.

Any idea why?

Stapes

 

[Dirk Goldgar]

In

Hi

I am trying to create a relationship, using Tools >> Relationships, &
keep getting this error message. I am trying to link table
TM_CampaignCustomers with TM_CompContact. TM_CampaignCustomers has as
its primary key field TM_CCID. TM_CompContact has PK_Contact. Bothe
are defined as Autonumber, Indexed, No Duplicates. The join is from
PK_Contact in TM_CompContact to FK_Contact in TM_CampaignCustomers.
FK_Contact is defined as indexed - duplicates OK.

When I tick the box 'Enforce Referential Integrity' in the Edit
Relationships dialogue - I get this error message.

Any idea why?

Not if everything you've said is correct. Please double-check that
field PK_Contact in table TM_CompContact is indexed, and does not allow
duplicates. Is that field designated as the table's primary key?

 

[scubadiver]

From your message your joins are completely confused. Simplify it by stating
it in list format (table then fields and state which is the primary key).

 

[BruceM]

Is FK_Contact a Number field (Long Integer). If PK_Contact is autonumber,
FK_Contact must be Long Integer.
If there are records in the tables already, try copying the tables and
pasting them as structure only, then creating the relationships between the
new empty tables. Any change?

 

[Stapes]

In





Not if everything you've said is correct. Please double-check that
field PK_Contact in table TM_CompContact is indexed, and does not allow
duplicates. Is that field designated as the table's primary key?

It is correct. PK_Contact in TM_CompContact is indexed, no duplicates,
and designated as the tables Primary Key.
TM_CCID in TM_CampaignCustomers is indexed, no duplicates, and
designated as the tables Primary Key.

 

[Stapes]

Is FK_Contact a Number field (Long Integer). If PK_Contact is autonumber,
FK_Contact must be Long Integer.
If there are records in the tables already, try copying the tables and
pasting them as structure only, then creating the relationships between the
new empty tables. Any change?










- Show quoted text -

BruceM - you are on the right track. FM_Contact in table
TM_CampaignCustomers is defined as number, long integer, indexed,
duplicates OK.
However, I did your experiment of copying the two tables structure
only, then creating the desired relationship between the two new
tables. I guess that must mean I have invalid or orphaned records in
there somewhere. Thanks.

 

[Stapes]

BruceM - you are on the right track. FM_Contact in table
TM_CampaignCustomers is defined as number, long integer, indexed,
duplicates OK.
However, I did your experiment of copying the two tables structure
only, then creating the desired relationship between the two new
tables. I guess that must mean I have invalid or orphaned records in
there somewhere. Thanks.- Hide quoted text -

- Show quoted text -

There were some TM_CampaignCustomer records with no matching
TM_CompContact record. I have deleted those, but I still get the
error. It is not compulsory for there to be a TM_CampaignCustomer
record. What I am trying to achieve is to prevent users from deleting
a TM_CompContact record if a TM_CampaignCustomer record exists.

 

[BruceM]

I have run across that situation where the error message outlives the
problem that generated the error message in the first place. I really don't
know what approach would work, but I would start with a Compact and Repair.
If still no luck, import all of the objects (tables, queries, forms, etc.)
into a new, blank database. To summarize that process, create a new
database. Click File > Get External Data > Import. Navigate to the old
database and make the selections. I think it is pretty self-explanatory at
that point. Click the Options button if you have any custom toolbars or
menu bars.
A query may help with diagnosis. Add both tables to the query, and set up
the join so that it is looking for all records from TM_CompContact. Use the
first option in Design View (Inner Join) and note the number of records.
Now use the third option (Right Join) and see if the number of records is
the same. If not, there is still a stray.