How to delete all the related table records when deleting a row in a table?

Question

I have a button to delete a customer. If a customer is deleted its getting deleted only from the customer table. But i have the customer's record in tables such as payments (where all the payment records are added) and comments (where all the user comments are added) .

I have customer id as foreign key in all tables. but its field name is different. for example in the customer_details table i have the customer id in the name of id and in other tables i have as lead_id.

what kind of query i should write to delete the records of a customer from all three (customer, comments, payments)tables ?

score 9 · Accepted Answer · answered Nov 26 '11 at 13:37

9

you should use the cascade property of foreign keys

for example

 FOREIGN KEY (customer_id) REFERENCES customer(id)
                      ON DELETE CASCADE

answered Nov 26 '11 at 13:37

LostMohican

3,082
7
29
38

is there a way in mysql to query the 'system' schema and, using that find out which key cascasdes link to an underlying base table. Basically 'reverse engineering' using the system info that must exist somewhere for all the cascades? I used to do this kinda stuff with Oracle. – Michael Durrant Nov 26 '11 at 13:42
@Michael Durrant, I had the exact same thought as your comment here as well as your comment on mine, lol. Here you go: http://stackoverflow.com/questions/806989/mysql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular – abelito Nov 26 '11 at 13:46
@MichaelDurrant yes, for every table that links to your main table, you should give this on delete cascade option – LostMohican Nov 26 '11 at 13:52
1

how can i use it as query ? can u explain lostmohican ? – Bala Nov 26 '11 at 14:15
@Bala.C you dont have to use it in a query, for example if you have the person id on person table and reference it with the comment table and in the definition of comment table you declared a column as a foreign key and ON DELETE CASCADE, when you delete the person from the person table, comments of the person are deleted automatically. I hope this is an answer to you : ) – LostMohican Nov 26 '11 at 18:50

score 0 · Answer 2 · answered Nov 26 '11 at 13:39

It would be better to delete from other tables first then delete from customer table at last. But you can delete from all the tables at once, use query something like this:

DELETE 
    FROM tbl_name1 , tbl_name2 ...
    USING table_references
    [WHERE where_condition]

Hope it helps

How to delete all the related table records when deleting a row in a table?

2 Answers2