Referential Integrity Problem

[burritonator]

The Access database that I am working on contains a table called "Devices" and another called "Hardware". The Hardware records contain a Foreign Key that links to the Primary Key of a Devices record. I have that relationaship set up, with Referential Integretity enforced ("Cascade Update Related Fields" and "Cascade Delete Related Fields" are not selected.)

I just discovered that Access will not allow me to either delete or modify a record in the Devices table if there are related records in the Hardware table. This is the desired behavior as it pertains to deletions. However, I need for the user to be able to modify records in the Devices table even if there are related records in the Hardware table. I do not understand why Access will not allow this, as I did not think that referential integrity rules would be violated when Device records are updated as long as the value of the Primary key doesn't change.

Is there a way that I can enforce referential integrity in such a way that record deletions in the primary table are not allowed if there are related records in other tables, while still allowing modifications to be made to records in the primary table?

Thanks,
Burritonator

 

[danvlas]

Records can be modified. Except the primary key or the fields that make a unique index and are used in a one-to-one/many relationship. Or your changes would duplicate the index.

Or...the database is corrupted...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[willir]

Dan -- you are back!

How is the new (now old?) job??

Burritonator
Is it possible you have a query and are trying to edit the data on the one-side table (device) and the many-side table (hardware) at the same time.

If so, a better approach is to edit one table at a time. For example, change the owner of the device -- this will not affect the "hardware".

You can also change the "hardware" such as add memory -- this will not affect the "device".

But when you try to change the device (eg. owner) and the hardware (eg. memory), Access may give you a rough time.

Richard

 

[danvlas]

Hey Richard,

I pop up in here from time to time, when I need to remember the 'good old days' and get rid of the paperwork that drives me nuts...

Thanks, it feels good to be remembered




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[burritonator]

I figured out the cause of the problem that I was having. Whenever a record was modified, the value of the Primary key was being overwritten. It wasn't actually being changed to a different value, but I assume that the fact that a value was being written to the key field was enough to cause Access to mark the field as having been modified.