1822, "Failed to add the foreign key constraint. Missing index for constraint

Question

I have 3 tables: users, photos, avatars. An avatar is a photo of user_1 that user_2 currently sees when scrolling through photos of user_1. Different users see different avatars for each user (depends on how many photos they have scrolled).

When user deleted, all his photos and avatars, that shown to other users also removes What I do wrong? (user_id I getting from server API, id just for convenience )

cursor.execute("""CREATE TABLE IF NOT EXISTS users(
id int AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER NOT NULL UNIQUE,
goal VARCHAR(255) DEFAULT NULL,
gender VARCHAR(255) DEFAULT NULL,
dob DATE DEFAULT NULL,
country VARCHAR(255) DEFAULT NULL,
city VARCHAR(255) DEFAULT NULL)""")

cursor.execute("""CREATE TABLE IF NOT EXISTS photos(
id INTEGER AUTO_INCREMENT,
user_id INTEGER NOT NULL,
photo VARCHAR(255) NOT NULL,
KEY (id),
PRIMARY KEY (user_id, photo),
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE)""")

cursor.execute("""CREATE TABLE IF NOT EXISTS avatars (
id INTEGER AUTO_INCREMENT PRIMARY KEY, 
user_id INTEGER, 
avatar VARCHAR(255),
shower_id INTEGER, 
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE, 
FOREIGN KEY (avatar) REFERENCES photos (photo) ON DELETE CASCADE,
FOREIGN KEY (shower_id) REFERENCES users (user_id) ON DELETE CASCADE)""")

But I get an error

1822, "Failed to add the foreign key constraint. Missing index for constraint 'avatars_ibfk_2' in the referenced table 'photos'")

version

mysqlsh.exe --version
C:\Program Files\MySQL\MySQL Shell 8.0\bin\mysqlsh.exe   Ver 8.0.19 for Win64 on x86_64 - for MySQL 8.0.19 (MySQL Community Server (GPL))

user_10806294 · Answer

Please try following query instead of third creation query.

ALTER TABLE photos ADD INDEX(photo);

CREATE TABLE IF NOT EXISTS avatars  (
  id INTEGER AUTO_INCREMENT PRIMARY KEY, 
    user_id INTEGER, 
    avatar VARCHAR(255),
    shower_id INTEGER, 
    FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE, 
    FOREIGN KEY (avatar) REFERENCES photos (photo) ON DELETE CASCADE,
    FOREIGN KEY (shower_id) REFERENCES users (user_id) ON DELETE CASCADE
);

You have to add index before apply foreign key.

user_5193536 · Answer

You need an index on photo

cursor.execute("""CREATE TABLE IF NOT EXISTS photos(
id INTEGER AUTO_INCREMENT,
user_id INTEGER NOT NULL,
photo VARCHAR(255) NOT NULL,
KEY (id),
KEY (photo),
PRIMARY KEY (user_id, photo),
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE)""")

See here https://dba.stackexchange.com/questions/268890/1822-failed-to-add-the-foreign-key-constraint-missing-index-for-constraint

1822, "Failed to add the foreign key constraint. Missing index for constraint

2 Answers