Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails?

2
votes

I'm helping a friend with his database, getting rid of errors for him, It keeps generating Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails.

Any idea whats up with it? i've been looking at it for an hour and cant find out what keeps failing.

EDIT: UPDATED CODE

--DROP TABLES

DROP TABLE Reviews;

DROP TABLE Boardgames_Owned;

DROP TABLE Boardgames;

DROP TABLE Store;

DROP TABLE Friends;

CREATE TABLE Reviews ( ReviewerID int(4) NOT NULL, Score varchar(10), comments varchar(50) NOT NULL, Date_of_review varchar(10) NOT NULL, CONSTRAINT PKReviewerID PRIMARY KEY (ReviewerID));

CREATE TABLE Boardgames_owned ( Game_Name varchar(50) NOT NULL, time_played varchar(24), TTM_Ratio char(12), Total_Cost char(12) NOT NULL, Score char(10), Expansion_pack char(3), BoughtID int(4) NOT NULL, CONSTRAINT PKBoughtID PRIMARY KEY (BoughtID));

CREATE TABLE Boardgames ( Price decimal(12,2) NOT NULL, pre_order char(3), GameName varchar(50), CONSTRAINT PKGameName PRIMARY KEY (GameName));

CREATE TABLE Store ( StoreID int(4) NOT NULL, StoreName varchar(50) NOT NULL, price_avg decimal(12,2) NOT NULL, CONSTRAINT PKStoreID PRIMARY KEY (StoreID));

CREATE TABLE Friends ( StoreID int(4) NOT NULL, BoughtID int(4) NOT NULL, ReviewerID int(4) NOT NULL, Faddress varchar(50), Ffirstname varchar(20) NOT NULL, Flastname varchar(15) NOT NULL, Time_availible char(8) NOT NULL, Perfered_genre char(10), Money_spent decimal(12,2) NOT NULL, GameName varchar(50), CONSTRAINT PKFfristname PRIMARY KEY (Ffirstname), CONSTRAINT FKStoreID FOREIGN KEY (StoreID) REFERENCES Store(StoreID), CONSTRAINT FKGameName FOREIGN KEY (GameName) REFERENCES Boardgames(GameName), CONSTRAINT FKBoughtID FOREIGN KEY (BoughtID) REFERENCES Boardgames_owned(BoughtID), CONSTRAINT FKReviewerID FOREIGN KEY (ReviewerID) REFERENCES Reviews(ReviewerID));

--populating reviews table

INSERT INTO reviews (ReviewerID, Score, Comments, Date_of_Review) VALUES (1112, '5.0', 'Needs moar', '05-17-20');

INSERT INTO reviews (ReviewerID, Score, Comments, Date_of_Review) VALUES (1113, '5.0', 'Needs moar', '05-17-20');

INSERT INTO reviews (ReviewerID, Score, Comments, Date_of_Review) VALUES (1114, '5.0', 'Needs moar', '05-17-20');

INSERT INTO reviews (ReviewerID, Score, Comments, Date_of_Review) VALUES (1115, '5.0', 'Needs moar', '05-17-20');

INSERT INTO reviews (ReviewerID, Score, Comments, Date_of_Review) VALUES (1116, '5.0', 'Needs moar', '05-17-20');

INSERT INTO reviews (ReviewerID, Score, Comments, Date_of_Review) VALUES (1117, '5.0', 'Needs moar', '05-17-20');

--populating boardgames_owned

INSERT INTO boardgames_owned (Game_Name, time_played, TTM_Ratio, Total_Cost, Score, Expansion_pack, BoughtID) VALUES ('braka', '20.00', '1.1', '1.2', '1.3', 'yes', 1234);

INSERT INTO boardgames_owned (Game_Name, time_played, TTM_Ratio, Total_Cost, Score, Expansion_pack, BoughtID) VALUES ('braka', '20.00', '1.1', '1.2', '1.3', 'yes', 1235);

INSERT INTO boardgames_owned (Game_Name, time_played, TTM_Ratio, Total_Cost, Score, Expansion_pack, BoughtID) VALUES ('braka', '20.00', '1.1', '1.2', '1.3', 'yes', 1237);

INSERT INTO boardgames_owned (Game_Name, time_played, TTM_Ratio, Total_Cost, Score, Expansion_pack, BoughtID) VALUES ('braka', '20.00', '1.1', '1.2', '1.3', 'yes', 1236);

INSERT INTO boardgames_owned (Game_Name, time_played, TTM_Ratio, Total_Cost, Score, Expansion_pack, BoughtID) VALUES ('braka', '20.00', '1.1', '1.2', '1.3', 'yes', 1238);

INSERT INTO boardgames_owned (Game_Name, time_played, TTM_Ratio, Total_Cost, Score, Expansion_pack, BoughtID) VALUES ('braka', '20.00', '1.1', '1.2', '1.3', 'yes', 1239);

--populating boardgames

INSERT INTO boardgames (Price, pre_order, GameName) VALUES (25.99,'yes', 'ml2');

INSERT INTO boardgames (Price, pre_order, GameName) VALUES (25.99,'yes', 'ml2');

INSERT INTO boardgames (Price, pre_order, GameName) VALUES (24.99'yes', 'ml2');

INSERT INTO boardgames (Price, pre_order, GameName) VALUES (23.99,'yes', 'ml2');

INSERT INTO boardgames (Price, pre_order, GameName) VALUES (22.99,'yes', 'ml2');

INSERT INTO boardgames (Price, pre_order, GameName) VALUES (21.99,'yes', 'ml2');

--populating store

INSERT INTO store (StoreID, StoreName, price_avg) VALUES (1112,'bars',20.99);

INSERT INTO store (StoreID, StoreName, price_avg) VALUES (1113,'bars',20.99);

INSERT INTO store (StoreID, StoreName, price_avg) VALUES (1114,'bars',20.99);

INSERT INTO store (StoreID, StoreName, price_avg) VALUES (1115,'bars',20.99);

INSERT INTO store (StoreID, StoreName, price_avg) VALUES (1116,'bars',20.99);

--Populating friends table

INSERT INTO friends (Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName) VALUES (1111, 1234, 1234, '1234 scryamor blvd.', 'Adam', 'Smith', '5:00pm', 'RPG', 25, 'Pathfinder');

INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName) VALUES (1111, 1234, 1234, '5678 Goldjerry rd.', 'Brak', 'So', '3:00pm', 'War', 60, 'Risk');

INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName) VALUES (1111, 1234, 1234, '1337 West ave.', 'Peter', 'kliage', '2:00pm', 'War', 13, 'War for Westeros');

INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName) VALUES (1111, 1234, 1234, '101 Vault dr.', 'Oliver', 'Sear', '5:00pm', 'Card', 45, 'Munchkin');

INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName) VALUES (1111, 1234, 1234, '117 Sierra rd.', 'John', 'Chief', '9:00pm', 'Horror', 00, 'Arkham Horror');

INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName) VALUES (1111, 1234, 1234, '1489 klesa blvd.', 'Melissa', 'Darya', '11:00am', 'Dice', 24, 'Marvel Dice Masters');

INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName) VALUES (1111, 1234, 1234, '113 Mikalhertz rd', 'Catilyn', 'Senja', '5:00pm', 'War', 10, 'BattleCry');

INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName) VALUES (1111, 1234, 1234, '546 Alkatraz dr.', 'Vincent', 'Engstrom', '7:30pm', 'Card', 40, 'Lord Of The Rings: The Card Game');

-- OLD CODE--

--DROP TABLES

DROP TABLE Reviews;

DROP TABLE Boardgames_Owned;

DROP TABLE Boardgames;

DROP TABLE Store;


DROP TABLE Friends;


CREATE TABLE Reviews ( ReviewerID int(4) NOT NULL, Score decimal(2,2), comments varchar(50) NOT NULL, Date_of_review varchar(10) NOT NULL, CONSTRAINT PKReviewerID PRIMARY KEY (ReviewerID));


CREATE TABLE Boardgames_owned ( Game_Name varchar(50) NOT NULL, time_played varchar(24), TTM_Ratio decimal(12,2), Total_Cost decimal(12,2) NOT NULL, Score decimal(10,10), Expansion_pack char(3), BoughtID int(4) NOT NULL, CONSTRAINT PKBoughtID PRIMARY KEY (BoughtID));


CREATE TABLE Boardgames ( Price decimal(12,2) NOT NULL, pre_order char(3), GameName varchar(50), CONSTRAINT PKGameName PRIMARY KEY (GameName));


CREATE TABLE Store ( StoreID int(4) NOT NULL, StoreName varchar(50) NOT NULL, price_avg decimal(12,2) NOT NULL, CONSTRAINT PKStoreID PRIMARY KEY (StoreID));


CREATE TABLE Friends ( StoreID int(4) NOT NULL, BoughtID int(4) NOT NULL, ReviewerID int(4) NOT NULL, Faddress varchar(50), Ffirstname varchar(20) NOT NULL, Flastname varchar(15) NOT NULL, Time_availible char(8) NOT NULL, Perfered_genre char(10), Money_spent decimal(12,2) NOT NULL, GameName varchar(50), CONSTRAINT PKFfristname PRIMARY KEY (Ffirstname), CONSTRAINT FKStoreID
                      FOREIGN KEY (StoreID) REFERENCES Store(StoreID), CONSTRAINT FKGameName
                      FOREIGN KEY (GameName) REFERENCES Boardgames(GameName), CONSTRAINT FKBoughtID
                      FOREIGN KEY (BoughtID) REFERENCES Boardgames_owned(BoughtID), CONSTRAINT FKReviewerID
                      FOREIGN KEY (ReviewerID) REFERENCES Reviews(ReviewerID));

 --Populating friends table

INSERT INTO friends (Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName)
VALUES (1111,1234,1234,'1234 scryamor blvd.','Adam','Smith','5:00pm','RPG',25.00,'Pathfinder');


INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName)
VALUES (1111,1234,1234,'5678 Goldjerry rd.','Brak','So','3:00pm','War',60.00,'Risk');


INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName)
VALUES (1111,1234,1234,'1337 West ave.','Peter','kliage','2:00pm','War',13.00,'War for Westeros');


INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName)
VALUES (1111,1234,1234,'101 Vault dr.','Oliver','Sear','5:00pm','Card',45.00,'Munchkin');


INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName)
VALUES (1111,1234,1234,'117 Sierra rd.','John','Chief','9:00pm','Horror',00.00,'Arkham Horror');


INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName)
VALUES (1111,1234,1234,'1489 klesa blvd.','Melissa','Darya','11:00am','Dice',24.50,'Marvel Dice Masters');


INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName)
VALUES (1111,1234,1234,'113 Mikalhertz rd','Catilyn','Senja','5:00pm','War',10.00,'BattleCry');


INSERT INTO friends (StoreID, BoughtID, ReviewerID, Faddress, Ffirstname, Flastname, Time_availible, Perfered_genre, Money_spent, GameName)
VALUES (1111,1234,1234,'546 Alkatraz dr.','Vincent','Engstrom','7:30pm','Card',40.00,'Lord Of The Rings: The Card Game');

 */
2
mysqldatabase

2 Answers

1
votes

You create several tables and one of those tables has FOREIGN KEYs which means some registries depends on registries from other tables.

In your case, you insert data on friends table that reference all other tables (from your create script). Try to populate those first then the friends table.

0
votes

See

CREATE TABLE Friends ( StoreID int(4) NOT NULL, BoughtID int(4) NOT NULL, ReviewerID int(4) NOT NULL, Faddress varchar(50), Ffirstname varchar(20) NOT NULL, Flastname varchar(15) NOT NULL, Time_availible char(8) NOT NULL, Perfered_genre char(10), Money_spent decimal(12,2) NOT NULL, GameName varchar(50), CONSTRAINT PKFfristname PRIMARY KEY (Ffirstname), CONSTRAINT FKStoreID
                  FOREIGN KEY (StoreID) REFERENCES Store(StoreID), CONSTRAINT FKGameName
                  FOREIGN KEY (GameName) REFERENCES Boardgames(GameName), CONSTRAINT FKBoughtID
                  FOREIGN KEY (BoughtID) REFERENCES Boardgames_owned(BoughtID), CONSTRAINT FKReviewerID
                  FOREIGN KEY (ReviewerID) REFERENCES Reviews(ReviewerID));

The Friends table has constraints on the Store, BoardGames, Boardgames_owned and Reviews tables, meaning the values set in the fields for those corresponding foreign keys (StoreID, GameName, BoughtID and ReviewerID) must exist in those other tables.