MySQL foreign constraint delete on boolean change

Question

I was thinking about implementing a user registration system. Where will be 'users' and 'user_activation' tables.

if my tables looked like this: table -> fields:

users -> id,username,activated(boolean), etc.



user_activation -> users_id (foreign key constraint obviously),token, etc.

Now, I can cascade delete the token data from 'user_activation' if a user is deleted from 'users' table.

I was wondering how would I achieve the same when the 'activated' boolean value in 'users' table would be set to 'true'?

(once a user is activated , the token would be useless, so it should be deleted once user is activated).

Answer 1

I can't think of a way to do this with a foreign key, but you could use a trigger:

delimiter //

CREATE TRIGGER user_activation_tr
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.activated THEN
        DELETE FROM user_activation WHERE users_id = NEW.id;
    END IF;
END;//

delimiter ;

MySQL foreign constraint delete on boolean change

Sign up