How to compose query to delete rows from two tables in MySQL?

Question

SQL

There are three tables:
- gift_certificate (entity);
- tag (entity);
- gift_certificate_to_tag_relation (link table for many-to-many relationship).


The records in the link table gift_certificate_to_tag_relation are stored as follows:


I want to make a MySQL JOIN query to delete a row from the table gift_certificate (entity) . In order to remove a row from this table, the first step is to remove the row from the linking table gift_certificate_to_tag_relation .

Here is what I tried to do:
- option 1:

DELETE r, gc
FROM gift_certificate_to_tag_relation AS r
LEFT JOIN gift_certificate gc on r.gift_certificate_id = gc.id
WHERE gc.id = 9;

- option 2:

DELETE gift_certificate, gcttr
FROM gift_certificate
JOIN gift_certificate_to_tag_relation AS gcttr ON gift_certificate.id = gcttr.gift_certificate_id
WHERE gift_certificate.id = 9;

- option 3:

DELETE FROM gift_certificate, gcttr
USING gift_certificate
INNER JOIN gift_certificate_to_tag_relation AS gcttr ON gift_certificate.id = gcttr.gift_certificate_id
WHERE gift_certificate.id = 9;

But this results in an error:

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`gift_certificates_system`.`gift_certificate_to_tag_relation`, CONSTRAINT `gift_certificate_to_tag_relation_ibfk_1` FOREIGN KEY (`gift_certificate_id`) REFERENCES `gift_certificate` (`id`))

Answer 1

Set the foreign key properties to ON DELETE CASCADE. After that, child records will be deleted automatically when the parent record is deleted.

Answer 2

As an option:

START TRANSACTION;
delete from gift_certificate_to_tag_relation where gift_certificate_id = 1;
delete from gift_certificate where id = 1;
COMMIT;

MySQL fiddle