Deleting Records

September 28, 2020, 06:02:49 PM

Hi

I have a structure with three major tables i.e., soldier, incident and incident_soldiers. this is essentially the incident details or the people/soldiers involved in a particular incident.

Soldier Table Fields:
id
soldier_number

Incident Table fields
id
incident_number
incident_soldiers

Incident_soldiers table fields
id
soldier_number
incident_number

Relationship is like:

1 Incident -> Many incident_soldiers
In incident_soldiers, soldier details need to be input in order to track which soldiers are present in each incident

Requirement is that once i delete an incident, the corresponding records in incident_soldiers be deleted. it can be many records.
incident_number is primary key in Incident Table and also referenced as foreign key in Incident_soldiers Table

I can't understand what BEFORE DELETE and AFTER DELETE queries should i put. I have searched the forums and tried but still couldn't get it worked.

Support will be highly appreciated in this.

Re: Deleting Records

Reply #1 – October 02, 2020, 06:02:08 AM

What about changing the DB foreign key definition and change the actions of "ON DELETE" to "CASCADE"? (on MySQL but it should be similar on other DBs)

https://www.mysqltutorial.org/mysql-on-delete-cascade/

Re: Deleting Records

Reply #2 – December 17, 2020, 05:47:12 PM

Thanks

I sorted this out with the guidance provided