delete table with links

Question

I got this table in MySQL Database:

• country
• country_province
• country_province_municipality
• country_province_municipality_barangay

Here's the the Foreign Keys for each table:

• country.objid link to country_province.countryid
• country_province.objid link to country_province_municipality.provinceid
• country_province_municipality.objid link to country_province_municipality_barangay.municipalityid

So, the 4 tables are connected by each other by its objid.

QUESTION: I was about to delete the links of all the records of 4 tables using the country.objid only. How to do it?

The script should look something like this:

delete country, country_province, country_province_municipality, country_province_municipality_barangay where country.objid = 'country0001'

Answer 1

Give "on delete cascade" for your foreignkeys.

MySQL ON DELETE CASCADE referential action for a foreign key to delete data from child tables when you delete data from a parent table.

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