MySQL Problem with foreign key on n:m relation

Question

I have this database

create table ticket
(
  id int auto_increment primary key,
  name varchar(100)
);

create table document
(
  id int auto_increment primary key,
  name varchar(100)
);

create table ticket_document
(
  ticket_id int,
  document_id int
);

insert into ticket (id, name) VALUES (1, "a"),(2,"b");
insert into document (id, name) VALUES (1, "x"),(2,"y");
insert into ticket_document (ticket_id, document_id) VALUES (1,1),(1,2),(2,2);

So every ticket can have multiple documents and each document can be referenced to multiple tickets.

I want to achieve, that if I delete a ticket all references to his documents are deleted AND if there is no more reference to one of the document from an other ticket the document is also deleted. But I don't know how to set the foreign keys.

Answer 1

Create foreign keys:

ALTER TABLE ticket_document
ADD FOREIGN KEY (ticket_id) REFERENCES ticket (id) ON DELETE CASCADE ON UPDATE CASCADE,
ADD FOREIGN KEY (document_id) REFERENCES document (id) ON DELETE CASCADE ON UPDATE CASCADE;

After ticket(s) deletion delete rows from document explicitly (or use service event procedure):

DELETE 
FROM document 
WHERE NOT EXISTS ( SELECT NULL 
                   FROM ticket_document
                   WHERE ticket_document.document_id = document.id );

fiddle

---

Alternatively you may use AFTER DELETE trigger for auto-clearing document table:

CREATE TRIGGER tr_ad_ticket
AFTER DELETE
ON ticket
FOR EACH ROW
DELETE 
FROM document 
WHERE NOT EXISTS ( SELECT NULL 
                   FROM ticket_document
                   WHERE ticket_document.document_id = document.id );

fiddle