DELETE tickets, archive, files
FROM tickets
LEFT JOIN ticket_files
ON ticket_files.ticket_id = tickets.ticket_id
LEFT JOIN archive
ON archive.document_id = ticket_files.document_id
LEFT JOIN files
ON files.file_id = ticket_files.file_id
WHERE tickets.ticket_id = 21
When the ticket doesn’t have any record in the join table ticket_files then the ticket is deleted correctly but if there is more than one record in the ticket_files then only one record is deleted from the tables archive and files. How can i make sure all the joined records are deleted also from archive and files? many thanks
It would help to know which database you’re using. Different engines work differently and have different constrictions.
One of those constrictions could be that you can’t leave an orphan record and cascade works differently.
Not that I can help you much. I haven’t tried deleting records from various tables at the same time like you’re trying to do.
But I do know that sometimes the differences between databases are important.
I have seen the type of query you’re trying to do with left joins and with inner joins.
You may want to try to use an inner join and see what happens, or switch the order of the relationships. With some databases, that helps.
I would have to read the documentation. I am assuming that you’re using InnoDB and if I remember well (and don’t trust me because my memory is really crap) InnoDB would need a trasaction, meaning that you would have to do something like
START TRANSACTION;;
DELETE
FROM tableA
WHERE id= 1
DELETE
FROM tableB
WHERE id= 1;
...
COMMIT;
I also confess that it is too early in the morning for me to think logically and maybe when I come back to this thread in a few hours I will see the problem right away
In principle, the query looks OK for me. I suggested the chang in the other of the left joins because, funny enough, it happened to me in the past. Just by changing the order everything started to work.
It could be something as simply as instead of writing left join on a.id=b.id, writing left join on b.id=a.id.
I agree with you but I can assure that it happened to me a couple of times that just by changing the order, the query worked.
Does it make any sense? Not at all. But it happened.
Hi I’ve tried changing the order and unfortunately still doesn’t work. I believe I can fix this with two queries. I can check if there are any files associated to a ti check first with a select query and then use two different delete queries according to the select result. I was just hoping I could do it with one query only also avoiding the use of transactions in mysql
Hi @Thallius the table tickets_files does have three columns ticket_id file_id and document_id and they are linked with foreign keys with the tables tickets, files and archive
That is why I don’t understand why it only deletes one record from table files and archive if I’ve got for example two records related to same ticket inside ticket_files
The problem is you have set the foreign keys for the tables files and archive in the wrong place. (ticket_files)
Foreign Keys belong in the child tables. Tables files and archive are the children of ticket files so each one of those tables needs a foreign cascading key in each one of those tables.
So, to fix, delete the two keys from ticket_files for those tables and then add a key to each of the tables files and archive. The other key to table tickets is OK as is.
After that, all you have to do is delete from the tickets table and the deletes will cascade down through the other 3 tables.
This is all the query you will need to delete a record from all the tables.
DELETE FROM tickets WHERE ticket_id = xxx
FYI: The order of your data inserts matter. First tickets, then ticket_files, then either of archive and/or files.
Hi @benanamen many thanks for your help.
I thought I could use the archive and files tabels (which I now thinking I can merge into one table) as general tables not only for all files associated to tickets but also for files associated to other tables in the database (for example I’ve got a table communications) and this is the reason why I tried to use a junction table (many-to-many) rather than add a foreign key into archive or files tables (as the foreign key should then refer to table communication and tickets not sure if is possible).
Do you suggest to use different file and archive tables for communications and tickets? Also from what you said than do I still need the ticket_files table? Sorry maybe is me misunderstanding
The solution is to the problem “How can i make sure all the joined records are deleted also from archive and files?” with the tables you are currently using.
I have no idea what you have going on so I am not able to advise on a proper schema. That would also be discussion for a new thread. You would need to provide a high level overview of what you are doing and the problems you want to solve if you would like our advice on how to design your database.
Hi @benanamen thanks for your reply. I think for now I’ll use two queries: first one to check if the ticket_id has got values in the ticket_files table and then if there are values use the following query:
DELETE archive, files
FROM ticket_files
LEFT JOIN archive
ON ticket_files.document_id = archive.document_id
LEFT JOIN files
ON files.file_id = ticket_files.file_id
WHERE ticket_files.document_id = :ticketD
Otherwise just delete the ticket with the following query
DELETE FROM tickets WHERE ticket_id = :ticketD
Because the first query obviously doesn’t work if the ticket doesn’t have files associated to it. I was hoping I could do it with one query but it is not possibile with the database structure I’ve got. Many thanks
Hi @benanamen i want to delete the ticket and if there are files associated then also delete the files. I’ve created a new table ticket_archive where I put a fk referring to tickets’ ID and on delete cascade the relation now is one to many and not many to many (my mistake) in this way deleting the ticket does cascade to files as well. Many thanks for your suggestions and help as always
