WithoutBrain1994 WithoutBrain1994 - 3 months ago 12
MySQL Question

How to update foreign key value when parent row is deleted in mysql?

I want to update the foreign key value when parent row is deleted.

There is a "SET NULL" option in phpmyadmin, but instead of setting the value to null, I want to set it to something I want.

How can I do this?

Answer

There's no auto-magical built-in way of doing this like the set null option, but you could always write a trigger with your specific business logic:

DELIMITER //

CREATE TRIGGER parent_delete_tr
BEFORE DELETE
    ON parent FOR EACH ROW

    BEGIN
        UPDATE children
        SET    parent_id = 123 -- Or some other logic
        WHERE  parent_id = OLD.id;
    END; //

DELIMITER ;