Error: can not delete or update parent row

Question

I'm trying to Delete all the records related to this supplier from all relevant tables for Supplier with supplierNum = S3.

But when I try to delete, I get an error

Can not delete or update a parent row

I already ready few topics here about it but could not figure out a solution. can somebody please help me out?

CREATE TABLE supplier 
(
    supplierNum CHAR(2) NOT NULL,
    name CHAR(10) NOT NULL,
    status TINYINT(4) NOT NULL,
    city VARCHAR(10) NOT NULL,

    PRIMARY KEY (supplierNum)
);

CREATE TABLE parts 
(
    partNum CHAR(2) NOT NULL,
    name CHAR(10) NOT NULL,
    colour CHAR(8) NOT NULL,
    weight DECIMAL(3,1) NOT NULL,
    city VARCHAR(10) NOT NULL,

    PRIMARY KEY (partNum)
);

CREATE TABLE supplies 
(
    supplierNum CHAR(2) NOT NULL,
    partNum CHAR(2) NOT NULL,
    quantity SMALLINT(6) NOT NULL,

    PRIMARY KEY (supplierNum, partNum),
    FOREIGN KEY (supplierNum) 
            REFERENCES supplier (supplierNum),
    FOREIGN KEY (partNum) 
            REFERENCES parts (partNum)
);

So when you get this error which table are you deleting from? — RiggsFolly, Dec 08 '21 at 12:02
First make sure that there are no Supplies with that suppliesNum (3). You could also set it to cascade or null on delete. — Cetin Basoz, Dec 08 '21 at 12:02
Because you have a foreign key in the `supplies` table, those rows have to be deleted BEFORE you delete the `supplier` — RiggsFolly, Dec 08 '21 at 12:03
Foreign key definitions have no ON DELETE action, hence you cannot delete rows from the tables which you refer on until at least one row referred to the deleted row is present in current table. — Akina, Dec 08 '21 at 12:08
You should have given the complete error message: `ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`supplies`, CONSTRAINT `supplies_ibfk_1` FOREIGN KEY (`supplierNum`) REFERENCES `supplier` (`supplierNum`))` And you should NOT have stopped reading after what you write in the title of your question., because "a foreign key constraint fails" is the cause see: [Cannot delete or update a parent row: a foreign key constraint fails](https://stackoverflow.com/questions/1905470/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails) — Luuk, Dec 08 '21 at 12:12

score 0 · Answer 1 · answered Dec 08 '21 at 12:11

If you need to delete all related rows from supplies while deleting the row from supplier table then you must add ON DELETE CASCADE option to the FOREIGN KEY definition.

If you want to remove unused rows from parts after above deletion (if such unused rows occures) them you must use trigger logic - such clearing cannot be performed with cascade action.

Error: can not delete or update parent row

1 Answers1