1822, "Failed to add the foreign key constraint. Missing index for constraint
Your problem with the table photos

is that you want to make qa reference to photo varchar(255) NOT NULL

But in mysql you need that column to be INDEXED
So make your query like this

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` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `photo` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`,`photo`),
  KEY `id` (`id`),
  KEY `photo` (`photo`),
  CONSTRAINT `photos_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
""")

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 photos (id) ON DELETE CASCADE
)""")


there was another problem, that you referende the same userid to user_id and shower_id, that also gets you an error, bit i beleive you are missing only a showwer table.