CODEWITHSUNDEEP
mysql
A.G
A.G

Reputation: 549

How to delete all rows from a table which references another table?

DELETE FROM hk_qa.shipping_order_lifecycle
WHERE create_dt < DATE_SUB(NOW(), INTERVAL 4 MONTH)

But its showing below error

Cannot delete or update a parent row: a foreign key constraint fails (hk_qa.lifecycle_reason, CONSTRAINT fk_lifecycle_reason_shipping_order_lifecycle1 FOREIGN KEY (shipping_order_lifecycle_id) REFERENCES shipping_order_lifecycle (id) ON DELETE NO ACT)

Upvotes: 0

Views: 863

Answers (1)

Shadow
Shadow

Reputation: 34305

If you have child records referencing the given records in shipping_order_lifecycle table, then either you need to delete the child records first, or change the foreign key constraint to cascade deletes using ON DELETE CASCADE in place of ON DELETE NO ACT.

I do not suggest to remove the constraint and do the delete because that would result in orphans records in the child tables and you do not want that.

Upvotes: 1

Related Questions