Query to delete primary & foreign key in same time

Question

Hello everybody i have two tables which are this structure

invoice table :

invoice_id
customer_name
order_date

invoice_infos :

id
invoice_id
production_name
product_prix
qtt

Now I wanna make a query which delete in same time a row which are the same invoice_no in two tables

Answer 1

You are looking for the Multiple-Table DELETE Syntax :

DELETE invoice, invoice_infos
FROM invoice
INNER JOIN invoice_infos ON invoice.invoice_id = invoice_infos.invoice_id
WHERE invoice.invoice_id = ?;

You can replace the ? with the id of the invoice that you want to get rid of.

Answer 2

if two tables are related and you are considering to delete record(s) from invoice_infos table whenever a record deleted from invoice table, you need FOREIGN KEY with CASCADE DELETE

alter table invoice_infos
add constraint invoice_fkey
foreign key (invoice_id)
references invoice(invoice_id)
on delete cascade;

after that whenever you issue

DELETE FROM invoice WHERE invoice_id = 5;

the related values on invoice_infos will also get deleted