Skip to main content
Database Administrators

Return to Question

formatted code
Source Link
Lennart - Slava Ukraini
Lennart - Slava Ukraini
  • 23.6k
  • 3
  • 33
  • 71

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?

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?

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?

Improved title, tags, formatting.
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 423
  • 663

MySQL - using ON DELETE CASCADE properly?not working

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)))

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 CASCADEON 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?

MySQL - using ON DELETE CASCADE properly?

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?

ON DELETE CASCADE not working

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?

Source Link
Lucifer
Lucifer
  • 21
  • 1
  • 1
  • 2

MySQL - using ON DELETE CASCADE properly?

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?