Jaime Dolor jr. Jaime Dolor jr. - 1 year ago 82
MySQL Question

How do I create SQL Delete that will also delete related records

I have two tables in my mySQL database: INVOICE and INVOICE_LINE. Both tables have a primary key column (auto-increment) ID. INVOICE_LINE has a column INV_ID which is related to INVOICE.ID i.e. relationship was created by defining column INVOICE_LINE.INV_ID as foreign key to INVOICE.ID.

How would I write an SQL for this?

Answer Source

Method 1:

Enforce foreign key constraints between these two tables INVOICE and INVOICE_LINE having ON DELETE CASCADE behavior. Thus deleting an entry from INVOICE table will delete all the corresponding records in INVOICE_LINE table.

Method 2:

If you don't have foreign key constraint between these two tables then you need to adopt the following query:

DELETE inv,inv_line
LEFT JOIN INVOICE_LINE inv_line ON inv.ID = inv_line.INV_ID
WHERE inv.ID = ?


I've used LEFT JOIN instead of INNER JOIN because if any invoice has no corresponding invoice line in INVOICE_LINE table then INNER JOIN won't delete the record from INVOICE table. But LEFT JOIN does

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download