CODEWITHSUNDEEP
mysqlforeign-keysprimary-keycomposite-primary-keymysql-error-1452
Oscar
Oscar

Reputation: 23

Why is error 1452 appearing when executing these tables?

I want to insert some data into some tables. However, upon reaching statistics_per_year, mysql shoots out error code 1452 and I do not know why this is.

error:

17:54:58    INSERT INTO statistics_per_year(disease_continent_id,infected_id,dead_id) VALUES(2,3,4) Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`practice`.`statistics_per_year`, CONSTRAINT `statistics_per_year_ibfk_1` FOREIGN KEY (`infected_id`) REFERENCES `statistics_reference` (`infected_id`))    0.484 sec

code:

CREATE TABLE df(
    disease_continent_id INT NOT NULL PRIMARY KEY 
);
CREATE TABLE statistics_reference(
    infected_id INT NOT NULL,
    dead_id INT NOT NULL,
    per_100000_population VARCHAR(30) NOT NULL,
    PRIMARY KEY(infected_id)
);
CREATE TABLE statistics_per_year(
    disease_continent_id INT NOT NULL,
    infected_id INT NOT NULL,
    dead_id INT NOT NULL,
    FOREIGN KEY(infected_id) REFERENCES statistics_reference(infected_id),
    FOREIGN KEY(disease_continent_id) REFERENCES df(disease_continent_id)
);
INSERT INTO df(disease_continent_id)
VALUES(1);
INSERT INTO statistics_reference(infected_id,dead_id,per_100000_population)
VALUES(1,2,"fff");
INSERT INTO statistics_per_year(disease_continent_id,infected_id,dead_id)
VALUES(1,3,4);

Upvotes: -1

Views: 91

Answers (1)

GMB
GMB

Reputation: 222672

Your schema stays:

CREATE TABLE statistics_per_year(
    ...
    FOREIGN KEY(infected_id) REFERENCES statistics_reference(infected_id),
    ...
);

This means: every value that you insert in statistics_per_year(infected_id) must exist in statistics_reference(infected_id).

You do:

INSERT INTO statistics_reference
    (infected_id,dead_id,per_100000_population) 
    VALUES(1,2,"fff");
INSERT INTO statistics_per_year
    (disease_continent_id,infected_id,dead_id) 
    VALUES(1,3,4);

Table statistics_reference contains just one record, with infected_id = 1. You are trying to insert a record in dependant table statistics_per_year with infected_id = 3. This fails because there is no parent record for that value in statistics_reference.

You can either insert the missing parent record in statistics_reference, or change the insert in the child table to reference an existing parent id.

Upvotes: 0

Related Questions