CODEWITHSUNDEEP
zend-framework2
Skylink
Skylink

Reputation: 83

Delete all Child data from MySQL database tables when Parent data is deleted in ZF2

I have a simple project in Zf2. I am using MySql Database. I have following tables in database.

1. provinces

2. districts : It is depended on provinces table. i.e, id of provinces table is foreign key in this table.

3. cities : It is depended on districts table. i.e, id of districts table is foreign key in this table.

I want to delete all the relevant data from depended tables when data from parent tables are deleted. e.g.,

1.when data from provinces table are deleted then data from districts and cities tables should be deleted.

  1. when data from districts table are deleted then data from cities tables should be deleted.

I have functions in their respective tables in Project_Name\module\Module_Name\src\Module_Name\Model

e.g., function to delete provinces is:

 public function deleteProvinces($id)
 {
     $this->tableGateway->delete(array('id' => (int) $id));
 }

$id is id of province to be deleted.

Function to delete districts is:

public function deleteDistricts($id)
 {
     $this->tableGateway->delete(array('id' => (int) $id));
 }

$id is id of district to be deleted.

Function to delete cities is:

public function deleteCities($id)
 {
     $this->tableGateway->delete(array('id' => (int) $id));
 }

$id is id of city to be deleted.

I thinked a lot, but don't find a solution for its doing. Any idea or suggestion is really very appreciated. Thanks in advance.

Upvotes: 0

Views: 316

Answers (2)

Raj
Raj

Reputation: 1284

Where ever needed use the service provided by the application, in this case for MySql while creating the table you can add FK constraint ON DELETE CASCADE. This work much faster than deleting the individual rows in depended tables

I had seen multi fold performance increase when using ON DELETE CASCADE when the row count started increasing

Constraint for districts table

CONSTRAINT `provinces_district` FOREIGN KEY (`provinces_id`) REFERENCES `provinces` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE

Constraint for cities table

CONSTRAINT `district_cities` FOREIGN KEY (`district_id`) REFERENCES `districts` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE

Upvotes: 1

PrinceG
PrinceG

Reputation: 982

You may use MySQL ON DELETE CASCADE Referential Action.

See Referential Actions in MySQL docs

Upvotes: 2

Related Questions