cascade delete problem

[danwand]

Hi,

I have a database with the following tables

tblStaffDetails tblGP
StaffID (Primary Key) GPID (Primary key)
name name
address address
telephone telephone
GPID (foreign key)
etc

so there is a 1 to M relationship between tblGP.GPID and tblStaffDetails.GPID

When a user deletes a tblGP record (i.e. GP(Dr Watson) has retired and therefore any staff member that used to have Dr Watson as their GP will no longer have a reference to this doctor, so tblSTaffDetails.GPID is either deleted or set to null until a new GP record is entered into tblGP and a new reference to that GP is set in tblSTaffDetails)

I thought this would be easy to accomplish by enforcing referential integrity and cascading deleted records. However this is not the case!!!

There are a number of other tables in the database that are all related to tblStaffDetails these include tblProfessionalTraining, tblEducation, tblNextofKin etc. Each of these tables have a field StaffID as the foreign key to relate the particular details to a specific member of staff (All 1 to M relationships).

So when i try to delete a GP record i get the message "cannot perform cascading operation. Since related records exist in table 'tblProfessionalTraing', referential integrity rules wold be violated."
I don't understand this message since all i want to delete is the tblGP record and the corresponing GPID for each staff member how had this GP.

The only way i found round this is to set enforce referential integrity (cascade update and cascade delete) for all tables in the database. However when i do this and then delete a GP record Access will allow me to do the delete but also deletes the tblstaffDetails record, so instead of just losing the GP record i lose all details about that member of staff as well!!!

Any advice would be appreciated

Dan

 

[RoyVidar]

Get a taste for the phrase "Cascade Delete Related Records". You've given a rather good explanation of how a relational database works (and is intended to work). It means delete all related records (not alter the foreign key field)...

Are you really, really sure you want to delete? It will also delete history. Anyway, some workaraounds:

1 - in stead of deleting the GP, add a field to the table indicating whether this GP is active or not (or other statuses too, for that sake), which can be used when querying the information
2 - include a "dummy" GP - "N/A", "Not assigned" or something, where you use a query to change all the "Dr Watson" references to this, this will loose history of course
3 - allow for NULL in the foreign key field of the tblStaffDetails table, and use a query to set all "Dr Watson" records to Null (the foreign key field) prior to deleting the record
4 - drop referential integrity, and do what you want - at the risk of creating orphan child records (and headaches)

Roy-Vidar

 

[danwand]

Thanks for the advice Roy - much appreciated.
I will probably use your 3rd suggestion.

Dan