Getting Error(1452) Cannot add or update a child row when using subquery on insert-CodePudding

Using mysql server (version 8.0.30) running on a laragon server I have the following tables (required shown): `

CREATE TABLE Accommodation_Preference(
    AccomPreference_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    BandType VARCHAR(255) NOT NULL,
    Gender VARCHAR(255) NOT NULL,
    Alcohol_Free CHAR(1) NOT NULL,
    Quietness CHAR(1) NOT NULL,
    
    PRIMARY KEY(AccomPreference_ID)
);

    
CREATE TABLE Address(
    Address_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    DoorNumber VARCHAR(255) NOT NULL,
    Street VARCHAR(255) NOT NULL,
    City VARCHAR(255) NOT NULL,
    Postcode VARCHAR(255) NOT NULL,
    
    PRIMARY KEY(Address_ID)
);

CREATE TABLE Accommodation_Details
(
    AccomDetails_ID INT UNSIGNED NOT NULL,
    Room_No VARCHAR(255) NOT NULL,
    Band VARCHAR(255) NOT NULL,
    Availability CHAR(1) NOT NULL,
    Total_Cost INT NOT NULL,
    Address_ID INT UNSIGNED NOT NULL UNIQUE,
    
    PRIMARY KEY(AccomDetails_ID),
    FOREIGN KEY(Address_ID) REFERENCES Address(Address_ID)
);

CREATE TABLE Tenant
(
    URN INT UNSIGNED NOT NULL,
    Title VARCHAR(255) NOT NULL,
    Forename VARCHAR(255) NOT NULL,
    Surname VARCHAR(255) NOT NULL,
    AccomDetails_ID INT UNSIGNED NOT NULL,
    AccomPreference_ID INT UNSIGNED NOT NULL,
    
    PRIMARY KEY(URN),
    FOREIGN KEY(AccomPreference_ID) REFERENCES Accommodation_Preference(AccomPreference_ID),
    FOREIGN KEY(AccomDetails_ID) REFERENCES Accommodation_Details(AccomDetails_ID)
);

And I am inserting these values into them:

INSERT INTO Address (Address_ID, DoorNumber, Street, City, Postcode)
VALUES (1, "26", "Basketball Lane", "Surrey", "SW4 0DF"),
       (2, "2", "ONCE Housing", "Surrey", "PO9 3HJ"),
       (3, "18", "Petits Filous Road", "Surrey", "BA2 7JW"),
       (4, "7A", "Sanpei Heights", "Surrey", "SAN 1HJ"),
       (5, "10B", "Forehead Road", "Surrey", "PO9 3HZ"),
       (6, "9B", "Golden Gate Towers", "Surrey", "BF5 LQZ"),
       (7, "12f", "Hudson River", "Surrey", "GG5 LFZ"),
       (8, "9B", "Sora Close", "Surrey", "AG5 LFZ");

INSERT INTO Accommodation_Preference (BandType, Gender, Alcohol_Free, Quietness)
VALUES ("A", "Mixed", "N", "N"),
       ("B", "Mixed", "Y", "N"),
       ("C", "Male", "N", "N"),
       ("D", "Female", "Y", "Y"),
       ("D", "Male", "Y", "Y");

INSERT INTO Accommodation_Details (AccomDetails_ID, Room_No, Band, 
            Availability, Total_Cost, Address_ID)
VALUES (1, "1A", "A", "N", 7000, (SELECT Address_ID FROM Address WHERE Street = "Basketball Lane")), 
       (2, "1B", "A", "N", 6950, (SELECT Address_ID FROM Address WHERE Street = "Basketball Lane")),
       (3, "1C", "A", "N", 7000, (SELECT Address_ID FROM Address WHERE Street = "Basketball Lane")), 
       (4, "1D", "A", "Y", 6950, (SELECT Address_ID FROM Address WHERE Street = "Basketball Lane")),
       (5, "1E", "A", "N", 6950, (SELECT Address_ID FROM Address WHERE Street = "Basketball Lane")),
       (6, "2A", "B", "N", 7000, (SELECT Address_ID FROM Address WHERE Street = "ONCE Housing") ),   
       (7, "2B", "B", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "ONCE Housing") ),
       (8, "2C", "B", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "ONCE Housing") ),
       (9, "2D", "B", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "ONCE Housing") ),
       (10, "2E", "B", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "ONCE Housing") ),
       (11, "2F","B", "N", 7000, (SELECT Address_ID FROM Address WHERE Street = "ONCE Housing") ),
       (12, "3A", "C", "N", 7000, (SELECT Address_ID FROM Address WHERE Street = "Petits Filous Road")),
       (13, "3B", "C", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "Petits Filous Road")),
       (14, "3C", "C", "N", 7000, (SELECT Address_ID FROM Address WHERE Street = "Petits Filous Road")),
       (15, "3D", "C", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "Petits Filous Road")),
       (16, "3E", "C", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "Petits Filous Road")),
       (17, "4A", "D", "N", 7000, (SELECT Address_ID FROM Address WHERE Street = "Sanpei Heights")),
       
       (18, "4B", "D", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "Sanpei Heights")),
       (19, "4C", "D", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "Sanpei Heights")),
       (20, "4D", "D", "N", 7000, (SELECT Address_ID FROM Address WHERE Street = "Sanpei Heights")),
       (21, "4E", "D", "Y", 7000, (SELECT Address_ID FROM Address WHERE Street = "Sanpei Heights")),
       (22, "5A", "B", "N", 4530, (SELECT Address_ID FROM Address WHERE Street = "Forehead Road")),
       (23, "14A", "D", "Y", 6900, (SELECT Address_ID FROM Address WHERE Street = "Golden Gate Towers")),
       (24, "F7",   "B", "Y", 4530, (SELECT Address_ID FROM Address WHERE Street = "Hudson River")),
       (25, "F8", "B", "Y", 4530, (SELECT Address_ID FROM Address WHERE Street = "Sora Close"));
       
       
INSERT INTO Tenant(URN, Title, Forename, Surname, AccomDetails_ID, AccomPreference_ID)
VALUES (6779712, "Mr", "Giannis", "Antetokoumpo", 1, 5), 
       (6354721, "Mr", "Stephen", "Curry", 2, 2),
       (6438443, "Ms", "HJ", "Evelyn", 6, 2),
       (6324397, "Mr", "Lebron", "James", 12, 5),
       
       (6324321, "Mr", "Xing", "Ze", 17, 5),
       (6218732, "Mr", "Hirotoshi", "Kiyono", 22, 5),
       (6092387, "Mr", "Yvon", "Lim", 3, 5),
       (6345678, "Mr", "Luka", "Doncic", 14, 5),
       (6120935, "Mr", "Rui", "Hachimura", 20, 5), 
       (6923628, "Mr", "Klay", "Thompson", 22, 5),
       (6287160, "Mr", "Devin", "Booker", 11, 5),
       (6437894, "Ms", "Tzuyu", "Chou", 5, 4);

On further research and trying things out, I found that this error is either caused by inserting data in the child table which does not yet exist/does not match in the parent table but on inspection it is not clear what does not exist.

Is there a way to find out what is missing from a child table?

CodePudding user response：

The Accommodation_Details table has a unique column:

Address_ID INT UNSIGNED NOT NULL UNIQUE,

So the INSERT to that table failed:

ERROR 1062 (23000): Duplicate entry '1' for key 'accommodation_details.Address_ID'

That is, the whole INSERT failed, so none of the 25 rows you tried to insert into the table were inserted. The table is empty.

mysql> select * from Accommodation_Details;
--------------
select * from Accommodation_Details
--------------

Empty set (0.00 sec)

Therefore all of the values you tried to reference as you did the subsequent INSERT to Tenant failed.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`tenant`, CONSTRAINT `tenant_ibfk_2` FOREIGN KEY (`AccomDetails_ID`) REFERENCES `accommodation_details` (`AccomDetails_ID`))

It's not a matter of which value is missing — all of them are missing.

Re your comment:

I tested this without the UNIQUE keyword, it works with no error.

Demo: https://dbfiddle.uk/jgOEpPiC

You also need to ALTER TABLE to remove the unique constraint/index, or else drop the table and recreate it without the unique constraint.

CodePudding user response：

Because your Accommodation_Details table Address_ID column has UNIQUE index.