0

The query was:

DELETE 
  FROM sql_invoicing.invoices
 WHERE client_id = 
 (SELECT client_id 
   FROM sql_store.customers 
  WHERE last_name = 'Smith')

In the above query the Database used has 'invoices' table in 'sql_invoicing' schema and 'customers' table is in 'sql_store' schema.

The error generated is :

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails ('sql_invoicing'.'payments',CONSTRAINT 'fk_payment_invoice' FOREIGN KEY ('invoice_id') REFERENCES 'invoices' ('invoice_id') ON UPDATE CASCADE)

Pardon me if the question is naive, I am a beginner in MySQL.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Swapnil Anand
  • 13
  • 1

1 Answers1

1

You have a table that has a foreign key from payments table to the invoice table. when you delete an invoice, what do you want to happen to the payment row that's connected to it? you have ON UPDATE CASCADE, that means if the invoice id changes, the payment's invoice_id value would change to follow, but it doesn't tell it what to do if the invoice is deleted all together.

You can add ON DELETE CASCADE, which would delete the payment related to the invoice you deleted.

But I would highly suggest against hard deletes like this, especially for important data like invoices and payments. instead I'd suggest going with a soft delete approach, a simple way would be adding another column "deleted", and not retrieving the fields where deleted is true. But I'd suggest thinking about your project's idea in-depth, why is an invoice deleted? was it cancelled, refunded? your database should reflect that, and so should your application in its logic (e.g can't make a payment for a cancelled invoice)

Mohamed Belkheir
  • 179
  • 1
  • 3
  • 1
    The database I'm working with is a training data set, so no harm done however much I modify it, but still your comment also gave me an idea about how to actually think while woring with SQL, Thank You for the helpful answer. Cheers! – Swapnil Anand Mar 21 '21 at 10:38