1

I have 3 tables: users, photos, avatars (photo of user_1 that currently see user_2) when user deleted, all his photos and avatars, that shown to other users also removes What I do wrong?

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

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

1 Answer 1

0

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.

4
  • I have similar example with reference to thre same user_id, but it is works ok. Similar example: (Unfortunately, I can't format line breaks in a comment in a good manner) cursor.execute("""CREATE TABLE IF NOT EXISTS shown_users ( id INTEGER AUTO_INCREMENT, user_id INTEGER, shown_id INTEGER, KEY (id), PRIMARY KEY (user_id, shown_id), FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE, FOREIGN KEY (shown_id) REFERENCES users (user_id) ON DELETE CASCADE)""") Jun 10, 2020 at 17:26
  • yes but you have there a combined key, which is missing in the question, as i don't know what you want to do exactly, i corrected the mistake s that come, so you can get further on your own
    – nbk
    Jun 10, 2020 at 17:31
  • Can you please a little bit explicit please, what key changes you did with indexes problem? I get you about combined PRIMARY KEY. BTW, why you writing in such a strange manner with " ` " symbols, I have met it already multiple times before. Jun 10, 2020 at 17:45
  • you referenced in avatars REFERENCES photos (photo) and i added KEY photo (photo) in the table photos.
    – nbk
    Jun 10, 2020 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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