Test your skills
Is there any possible way of not allowing changes with the primary key? Something called ON UPDATE RESTRICT?
The Fact you are dealing with has large connection to Referential integrity in relational database management systems. Referential integrity states Relationships between tables are valid. The main criteria are:
1. You cannot enter value in foreign key field of the Related Table that does not exist in the primary key (PK) of the primary table. However, you can enter a Null value/No value in the foreign key (FK).
2. You cannot change/delete a record from a primary table if the primary key value is used in records of an existing related table. MS Access 2003 enforces all the Constraints so that relationships are valid.
If you enter some records into the related tables then you can find that:
1. You cannot enter duplicate Primary key.
2. Cannot enter a foreign key value not present in Primary Key.
3. Cannot change/update a primary key value already used in a record as a foreign key.
If you are still facing the problem please look whether Cascade Update Related Fields is set. Disable it.