What is the accepted method of deleting records referenced by a join / join table?
I have three tables as follows: no cascading links (I don't want that as the database is mostly driven by NHibernate).
Invoice
(
entity_id int not null,
...
)
Ticket
(
entity_id int not null,
...
)
InvoiceTicket
(
InvoiceId --> Not-null foreign key to Invoice.entity_id
TicketId --> Not-null Foreign key to Ticket.entity_id
)
What I am trying to do is delete invoices, tickets and their associated InvoiceTicket lines, given the criteria in the Ticket. But I have to do this externally for our application, so I create a SQL query to do this.
I have already removed all dependencies for both invoice and ticket, no problem. Since Invoice and Ticket refer to InvoiceTicket, I have to delete the InvoiceTicket lines first. However, if I do this, my link to the table of bills is broken (I can still delete the tickets of interest, but not the bills anymore).
What is the accepted method for performing this operation using SQL?
I solved the problem already with a temporary table and filled it with the rows of interest from the InvoiceTicket table, but what other people are doing to solve this problem? I would suggest that you can do this with a stored procedure too, but I'm not that familiar with writing. Is there a direct way to accomplish this operation using SQL queries?
source to share
Well, this is how I would do it:
BEGIN TRANSACTION
DELETE FROM InvoiceTicket
WHERE EXISTS(
SELECT *
FROM TICKET t
WHERE {t.* conditions are met}
)
DELETE FROM Ticket
WHERE {t.* conditions are met}
DELETE FROM Invoice
WHERE NOT EXISTS(
SELECT *
FROM InvoiceTicket it
WHERE Invoice.entity_id = InvoiceTicket.InvoiceId
)
COMMIT TRANSACTION
It has been rightly pointed out that this approach (see above) only works if the invoices require at least one associated ticket. While this is true, this also begs the reverse question, do you really want to DELETE every invoice associated with your respective tickets? Because they can also be linked to other, non-deleted tickets.
source to share
I don't know if this is supported by all DBMSs, but in mySQL you can delete from JOIN tables. Something like:
DELETE Invoice, Ticket, InvoiceTicket
FROM Invoice, InvoiceTicket, Ticket
WHERE (condition on Ticket)
AND Ticket.Id = InvoiceTicket.TicketId
AND InvoiceTicket.InvoiceId = Invoice.Id
source to share
It has been rightly pointed out that deleting the invoices themselves can cause problems if those invoices reference invoicetickets that are not themselves deleted.
While true, I want to point out that I didn’t mean to suggest anywhere (and I didn’t say anywhere) that the set of facto-facts should be equal to the set of invoices, the identifier of which can be found in any case that should be deleted ...
If anyone has interpreted my post to mean this, I would like to warn these people that it is too difficult and too willing to make fears.
source to share