WithoutBrain1994 WithoutBrain1994 - 1 year ago 78
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 Source

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:


CREATE TRIGGER parent_delete_tr
    ON parent FOR EACH ROW

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download