I am currently coding in SQL (mySQLworkbench) and I am trying to add multiple foreign keys, but it keeps showing me the error code 1822 (failed to add the foreign key constraint. missing index for constraint), especially when I try to run this specific code:
ADD FOREIGN KEY (username) REFERENCES communityList(username);
Please can anyone help me out what I am doing wrong? Thank you!
Below is my whole code (do not worry, all the data is made-up fake data!):
CREATE DATABASE AnimeList;
USE AnimeList;
CREATE TABLE user
(username VARCHAR(50) PRIMARY KEY,
gmail VARCHAR(50),
password VARCHAR(20),
subscription CHAR(10),
sex CHAR(10),
age INT,
community VARCHAR(50)
);
INSERT INTO user
(username, gmail, password, subscription, sex, age, community)
VALUES
("neilmoore", "purpleneil@gmail.com", "heron1011", "essential", "male", 80, "mysterious death"),
("nicholaskennedy", "coppernicholas@gmail.com", "albatross2002", "premium", "male", 18, "lets go isekai"),
("lucilenunez", "whitelucile@gmail.com", "blackbird1704", "essential", "female", 64, "childhood nostalgia"),
("anneunderwood", "greyann@gmail.com", "crab2806", "standard", "female", 30, "rikejyo"),
("kehlanikane", "limekehlani@gmail.com", "blackcrazyant2606", "premium", "female", 18, "i am the heroine"),
("devainhopkins", "reddevian@gmail.com", "badger1709", "standard", "male", 48, "sportsmanship"),
("todneyadams", "silvertodney@gmail.com", "viper1209", "standard", "male", 19, "school life"),
("leomckenzie", "copperleo@gmail.com", "gentoopenguin1103", "premium", "male", 37, "kaguya lovers"),
("etheldoyle", "bronzeethel@gmail.com", "grizzlybear1408", "essential", "female", 45, "anya fans"),
("madisondaniel", "lilacmadison@gmail.com", "lapwing0609", "essential", "female", 41, "hungry rumbly"),
("simonetran", "whitesimone@gmail.com", "watershrew1206", "premium", "male", 32, "my own kingdom"),
("rondallhaley", "silverrondall@gmail.com", "wildboar0107", "standard", "male", 23, "boruto"),
("linahood", "goldlina@gmail.com", "snake2802", "premium", "female", 26, "movie night"),
("deanacraft", "yellowdeana@gmail.com", "lobster0402", "essential", "female", 36, "like ballerina"),
("suzettemaddox", "cyansuzette@gmail.com", "impala2604", "standard", "female", 26, "one piece"),
("rickcarr", "bluerick@gmail.com", "cichild2207", "premium", "male", 30, "boundary between war"),
("sapphirehendricks", "silversapphire@gmail.com", "trapdoorspider0610", "essential", "female", 20, "mysterious death"),
("tristramweiss", "redtristram@gmail.com", "locust1503", "standard", "male", 27, "boruto"),
("iriswynn", "orangeiris@gmail.com", "fairywren1302", "premium", "female", 24, "kaguya lovers"),
("joydavis", "turquoisejoy@gmail.com", "beaver0502", "essential", "male", 54, "school life"),
("ennisfitzpatrick", "crimsonennis@gmail.com", "seal0202", "standard", "male", 30, "boruto"),
("arnoldedwards", "skybluearnold@gmail.com", "fairywren1310", "premium", "male", 45, "lets go isekai"),
("louisecontreras", "indigolouise@gmail.com", "goshawk2409", "essential", "male", 18, "lets go isekai"),
("rileyatkins", "peachriley@gmail.com", "trumpeterswan1204", "standard", "male", 72, "school life"),
("annabeasley", "greenanna@gmail.com", "nightjar2511", "premium", "female", 33, "mysterious death"),
("judeannewman", "goldjudean@gmail.com", "thrush2204", "essential", "male", 61, "lets go isekai"),
("davionpatrick", "greydavion@gmail.com", "kitten1007", "essential", "male", 28, "one piece"),
("magdalenastevenson", "greenmagdalena@gmail.com", "dromedary2102", "essential", "female", 23, "kaguya lovers"),
("caitlinanthony", "crimsoncaitlin@gmail.com", "porpoise1902", "premium", "female", 44, "movie night"),
("marshagoodman", "lilacmarsha@gmail.com", "greywolf1602", "premium", "female", 30, "movie nigth"),
("jillstrong", "blackjill@gmail.com", "coralsnake2411", "standard", "female", 20, "boruto"),
("tysonfleming", "peachtyson@gmail.com", "pika2607", "standard", "male", 45, "mysterious death"),
("henleyriley", "aquamarinehenley@gmail.com", "blackmamba1709", "essential", "male", 69, "movie night"),
("staciesheppard", "aquamarinestacie@gmail.com", "otter2902", "essential", "female", 20, "like ballerina"),
("thomasgriffin", "goldthomas@gmail.com", "bat0111", "standard", "male", 40, "my own kingdom"),
("yvettefulton", "pinkyvette@gmail.com", "seal2707", "premium", "female", 57, "like ballerina"),
("alexleach", "slatealex@gmail.com", "squid2909", "standard", "female", 24, "sportsmanship"),
("alexanderson", "pinkalex@gmail.com", "bullfinch1011", "premium", "male", 81, "sportmanship"),
("rondallwhitfield", "lilacrondall@gmail.com", "africanelephant0511", "essential", "male", 33, "sportsmanship"),
("dajuanduran", "bronzedajuan@gmail.com", "osprey2103", "standard", "male", 24, "childhood nostalgia"),
("elsarobinson", "peachelsa@gmail.com", "woodcock1210", "standard", "female", 43, "childhood nostalgia");
CREATE TABLE communityList
(community VARCHAR(50) PRIMARY KEY,
type CHAR(50),
size CHAR(10),
genre CHAR(50),
username VARCHAR(50),
createdDate DATE
);
INSERT INTO communityList
(community, type, size, genre, username, createdDate)
VALUES
("mysterious death", "multiple anime", "medium", "mystery", "neilmoore", "2022-05-05"),
("lets go isekai", "topic", "large", "fantasy", "nicholaskennedy", "2022-06-12"),
("childhood nostalgia", "multiple anime", "large", "comedy", "lucilenunez", "1999-05-19"),
("rikejyo", "single anime", "small", "romance", "anneunderwood", "2022-04-28"),
("i am the heroine", "single anime", "medium", "romance", "kehlanikane", "2022-05-24"),
("sportsmanship", "topic", "large", "sports", "devainhopkins", "2022-07-19"),
("school life", "topic", "large", "comedy", "todneyadams", "2022-05-28"),
("kaguya lovers", "single anime", "small", "romance", "leomckenzie", "2022-07-17"),
("anya fans", "single anime", "medium", "action", "etheldoyle", "2022-06-01"),
("hungry rumbly", "multiple anime", "small", "slice of life", "madisondaniel", "2022-06-20"),
("my own kingdom", "single anime", "small", "action", "simonetran", "2022-06-30"),
("boruto", "single anime", "large", "action", "rondallhaley", "2020-03-04"),
("movie night", "topic", "large", "all", "linahood", "2022-05-28"),
("like ballerina", "multiple anime", "small", "drama", "deanacraft", "2022-07-03"),
("one piece", "single anime", "large", "adventure", "suzettemaddox", "2000-10-03"),
("boundary between war", "miltiple anime", "medium", "sci-fi", "rickcarr", "2022-06-03");
ALTER TABLE ratings
ADD FOREIGN KEY (animeName) REFERENCES animeList(animeName),
ADD FOREIGN KEY (username) REFERENCES user(username),
ADD FOREIGN KEY (username) REFERENCES communityList(username);
