how to optimize query

Question

Is there any single query to delete the child tables if the parent table will be deleted?

tables:

t1

t1_id 1

t2

t2_id t1_id 1 1 2 1

t3

t3_id t2_id 1 1 2 1 3 1

t4

t4_id t3_id 1 1 2 1

So if I will delete t1_id =1 , all children rows must also be deleted to avoid orphan data... In this case all data in these 4 tables should be deleted.. Is there any single line of query how to this?

Thank you so much.

Answer 1

You should have a look at using

FOREIGN KEY Constraints

with ON DELETE CASCADE

CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table.

Answer 2

Your best bet is to define foreign keys and declare the tables to cascade on delete. Have a look to the SQL syntax.