What is the accepted method to delete records referenced by a junction/join table?

Question

I have three tables as follows (please forgive the loose syntax), with NO cascading relationships (I do not want this, as the database is primarily managed 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'm trying to do is delete the invoices, tickets, and related InvoiceTicket rows given a criteria on Ticket. But I have to do this externally to our application, hence why I'm building a SQL query to do it.

I've already deleted all the dependencies to both Invoice and Ticket, no problem. Since Invoice and Ticket are referenced by InvoiceTicket, I must delete InvoiceTicket rows first. However, if I do that, then my link to the Invoice table is broken (I can still delete the tickets of interest, but no longer the invoices).

What is the accepted method to perform this operation using SQL?

I solved the problem already by using a temporary table and filling it with the rows of interest from the InvoiceTicket table, but what are other people doing to solve this type of problem? I would imagine you could do this with a stored procedure as well, but I'm not as familiar with writing those. Is there a direct way of doing this operation through SQL queries?

Answer 1

You could put the rows from InvoiceTicket into a temporary table, and then delete InvoiceTicket, Ticket and finally Invoice from the ids in the temporary table. Finally blow away the temporary table.

Answer 2

Well, here's 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 validly pointed out that this approach (above) only works if Invoices require at least one associated ticket. While this is true, it also raises the converse question, which is do you really want to DELETE every invoice associated with your matching tickets? Because they may also be associated with other, non-deleted tickets as well.

Answer 3

I don't know if that is supported by all DBMS, but in mySQL, you can delete from a JOIN on the 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

Answer 4

If Invoices can validly exist without associated invoicetickets then RBarryYoung's solution is crap. It deletes every such invoice.

In that case, in order to correctly determine the set of invoices to delete, you must first query them and set them aside.

Answer 5

It has been validly pointed out that deleting the invoices themselves might give rise to problems if those invoices are referenced by invoicetickets that are themselves not deleted.

Whilst true, I want to point out that nowhere have I meant to suggest (and nowhere have I actually said) that the set of invoices-to-be-deleted should be equal to the set of invoices whose id can be found in any invoiceticket-to-be-deleted.

If anyone interpreted my message to mean that, I would like to warn those people against the dangers of making assumptions too easily and too eagerly.