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?
Enforce foreign key constraints between these two tables
ON DELETE CASCADE behavior. Thus deleting an entry from
INVOICE table will delete all the corresponding records in
If you don't have foreign key constraint between these two tables then you need to adopt the following query:
DELETE inv,inv_line FROM INVOICE inv LEFT JOIN INVOICE_LINE inv_line ON inv.ID = inv_line.INV_ID WHERE inv.ID = ?
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