2

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?

  • I suppose your default database engine is InnoDB, right? If not, that would be the 2nd reason for the FKs not to have been created. – ypercubeᵀᴹ Nov 3 '14 at 21:37
  • Oh, that works, thank you. Why is it like that? – Lucifer Nov 3 '14 at 21:39
  • edited my answer. – ypercubeᵀᴹ Nov 3 '14 at 21:51
2

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 REFERENCES users (id) ON DELETE CASCADE,
    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
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.