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

Possible duplicate of [Mysql: delete rows in two tables with foreign keys](https://stackoverflow.com/questions/39768995/mysql-delete-rows-in-two-tables-with-foreign-keys) — Sam M, Feb 02 '19 at 23:25

score 2 · Answer 1 · answered Feb 02 '19 at 23:14

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

score 0 · Answer 2 · answered Feb 02 '19 at 23:13

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.

Query to delete primary & foreign key in same time

2 Answers2