我想在一些表中插入一些数据。然而,当到达statistics_per_year
时,mysql发出错误代码1452,我不知道为什么会这样
错误:
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
代码:
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);
您的模式保持:
CREATE TABLE statistics_per_year(
...
FOREIGN KEY(infected_id) REFERENCES statistics_reference(infected_id),
...
);
这意味着:在statistics_per_year(infected_id)
中插入的每个值都必须存在于statistics_reference(infected_id)
中。
您需要:
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);
表statistics_reference
只包含一条记录,其中包含infected_id = 1
。您正试图在具有infected_id = 3
的从属表statistics_per_year
中插入一条记录。此操作失败,因为statistics_reference
中没有该值的父记录。
您可以在statistics_reference
中插入丢失的父记录,也可以更改子表中的insert
以引用现有的父id。