Linking tables by autonumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created two tables and chose ID and autonumber as my
primary key in both and joined the tables. Referential
integrity and cascade on delete are accepted but when I
add cascade on update I get "invalid field
definition 'database ID' in definition of index or
relationship. I don't understand because Access created
the primary key?
 
Normally a join between tables like this would join a primary key autonumber
in one table with a foreign key long integer in the other table.
 
I created two tables and chose ID and autonumber as my
primary key in both and joined the tables. Referential
integrity and cascade on delete are accepted but when I
add cascade on update I get "invalid field
definition 'database ID' in definition of index or
relationship. I don't understand because Access created
the primary key?
Click to expand...

The Primary Key can be an Autonumber, but the foreign key - the field
you link to in the other table - CANNOT. It must be a Long Integer
field instead.

An Autonumber is a meaningless, arbitrary unique key. It makes no
sense to join two tables autonumber to autonumber, because there is no
way to control or predict which record in the second table is linked
to any particular record in the first.
 
Back
Top