mysql - 1822, “未能添加外键约束。缺少约束索引

Question

我有 3 个表:用户、照片、头像。头像是 user_2 当前在滚动 user_1 的照片时看到的 user_1 的照片。不同的用户会看到每个用户的不同头像(取决于他们滚动了多少张照片)。

当用户删除时，显示给其他用户的所有他的照片和头像也会删除我做错了什么？
(user_id 我从服务器 API 获取，id 只是为了方便)

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

但我收到一个错误

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

版本

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

Answer 1

你需要一个照片索引

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

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