CODEWITHSUNDEEP
mysql
Steven
Steven

Reputation: 697

How can I delete across multiple tables with one query?

I have two tables that look like the following :

Table 1 : invoices

Table 2 : invoice_details

public function deleteInvoice($id='')
    {
        if( !empty( $id ) ){
            $query = "DELETE FROM invoices where uuid ='$id'";
            if(mysqli_query($this->_con, $query))return true;   
            else return false;  
        }else{
            return false;   
        }
    }

Table 2 contains a column called "invoice_id" that relates to the "id" of Table 1

Whenever I remove an invoice using the above function, I also want to remove the invoice_details at the same time. Is there an easy way to modify the above code to do that?

Upvotes: 0

Views: 50

Answers (3)

TheLastOperator
TheLastOperator

Reputation: 111

First you need to put a foreign key constraint on invoice_details

ALTER TABLE invoice_details
    ADD CONSTRAINT fk_invoice
    FOREIGN KEY(invoice_id)
    REFERENCES invoices(id)
    ON DELETE CASCADE;

Then you can delete your invoice and it will delete the details automatically

Upvotes: 2

Richard St-Cyr
Richard St-Cyr

Reputation: 995

You could do this using foreign key constraints, forcing a CASCADE on delete. See https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html for additional details.

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

Don't modify the code. Have a foreign key relationship between the tables with an ON DELETE CASCADE option.

ALTER TABLE invoice_details ADD CONSTRAINT fk_invoice FOREIGN KEY invoice_id REFERENCES invoices(invoice_id) ON DELETE CASCADE;

Now the database does the job for you.

Upvotes: 1

Related Questions