ON DELETE CASCADE not working

Question

Trying to implement a simple user follower system in MySQL, using a junction table:

CREATE TABLE users (
    id int NOT NULL AUTO_INCREMENT, 
    email varchar(255) NOT NULL,
    username varchar(25) NOT NULL,
    password varchar(25) NOT NULL,
    apikey varchar(45) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(email),
    UNIQUE(username),
    UNIQUE(apikey)
);


CREATE TABLE followers (
    id_follower INT NOT NULL REFERENCES users (id) ON DELETE CASCADE, 
    id_following INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    PRIMARY KEY (id_follower, id_following)
);

I insert records for followers like this(using Python):

db.cursor.execute(
'''INSERT into FOLLOWERS(id_follower, id_following) 
values ((SELECT ID FROM USERS WHERE apikey = %s), %s)''',
(request.apikey, int(request.userid)))

I want it so that if a follower account is deleted, all follows for that account will be deleted, and if an account being followed is deleted, all follows for that account will be deleted as well. The ON DELETE CASCADE doesn't seem to be working, if I delete accounts, all follower records remain in the followers table. Am I not using it correctly?

Answer 1

You declared the FK constraints "inline" and you fell into a mysql trap. You can check with SHOW CREATE TABLE followers that there is no FK now! This known behaviour is mentioned in the mysql documentation:

MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

It should really say that the syntax is recognized as valid SQL but is ignored and the foreign key constraints are not created.

---

To solve the issue, the CREATE TABLE statement should have been:

CREATE TABLE followers (
    id_follower INT NOT NULL,
    id_following INT NOT NULL,
    PRIMARY KEY (id_follower, id_following),
    CONSTRAINT follower_fk
        FOREIGN KEY (id_follower) 
        REFERENCES users (id) ON DELETE CASCADE, 
    CONSTRAINT following_fk
        FOREIGN KEY (id_following) 
        REFERENCES users (id) ON DELETE CASCADE
);

If you already have data in the table, you can add the constraints with an ALTER TABLE statement:

ALTER TABLE followers (
    ADD CONSTRAINT follower_fk
        FOREIGN KEY (id_follower) 
        REFERENCES users (id) ON DELETE CASCADE, 
    ADD CONSTRAINT following_fk
        FOREIGN KEY (id_following) 
        REFERENCES users (id) ON DELETE CASCADE
);