mysql - 错误 1452 : Cannot add or update a child row: a foreign key constraint fails

Question

请帮帮我!

我遇到了这个错误。

Error: Cannot add or update a child row: a foreign key constraint fails (world.alarmes, CONSTRAINT fk_alarmes_registos1 FOREIGN KEY (idRegisto) REFERENCES registos (idRegisto) ON DELETE NO ACTION ON UPDATE NO ACTION)

我有这些 table 。

CREATE TABLE `registos` (
  `data_registo` char(10) NOT NULL,
  `hora_registo` time NOT NULL,
  `idSensor` varchar(8) NOT NULL,
  `Temperatura` char(6) DEFAULT NULL,
  `Humidade` char(6) DEFAULT NULL,
  `pt_orvalho` char(6) DEFAULT NULL,
  `idRegisto` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idRegisto`,`idSensor`,`data_registo`,`hora_registo`),
  KEY `fk_registos_sensores1_idx` (`idSensor`),
  CONSTRAINT `fk_registos_sensores1` FOREIGN KEY (`idSensor`) REFERENCES `sensores` (`idSensor`) ON DELETE NO ACTION ON UPDATE NO ACTION
) 




CREATE TABLE `alarmes` (
  `idAlarme` int(11) NOT NULL AUTO_INCREMENT,
  `descricao_alarme` varchar(45) DEFAULT NULL,
  `data_criacao` datetime DEFAULT CURRENT_TIMESTAMP,
  `idRegisto` int(11) NOT NULL DEFAULT ''0'',
  PRIMARY KEY (`idAlarme`,`idRegisto`),
  KEY `fk_alarmes_registos1_idx` (`idRegisto`),
  CONSTRAINT `fk_alarmes_registos1` FOREIGN KEY (`idRegisto`) REFERENCES `registos` (`idRegisto`) ON DELETE NO ACTION ON UPDATE NO ACTION
) 

当我插入表记录时，会弹出错误。

insert into registos values ('2014-03-31', '14:03:32', 'BrgTH032', '22.3', '45.3', '9.9', '32');

如果我这样做:

SET FOREIGN_KEY_CHECKS=0

下一个插入已被接受，但是当我重试时。返回给出相同的错误。

我一直在研究但失败了，因为 registos 表引用了sensores 表中的外键。如果所引用的表中没有相应的条目，则无法直接插入到关系表中。

但我不知道如何解决这个问题。

请帮助我。

--------编辑(我使用触发器来填充表 Alarmes)------------------------

DELIMITER $$
create TRIGGER alerta
BEFORE INSERT ON registos
FOR EACH ROW
begin
Set @tempmax=0;
Set @tempmin=0;


select lim_inf_temp, lim_sup_temp into @tempmin, @tempmax from sensores  where idSensor=NEW.idSensor;


Set @maxidAlarme=0;
if (CAST(NEW.Temperatura AS UNSIGNED)<@tempmin) then
SELECT MAX(idAlarme) into @maxidAlarme FROM alarmes;
SET @maxidAlarme=@maxidAlarme+1;
INSERT INTO alarmes(idAlarme,descricao_alarme, idRegisto) VALUES (@maxidAlarme,"temperatura inserida inferior ao normal",New.idRegisto);
end if; 


if (CAST(NEW.Temperatura AS UNSIGNED)>@tempmax) then
SELECT MAX(idAlarme) into @maxidAlarme FROM alarmes;
SET @maxidAlarme=@maxidAlarme+1;
INSERT INTO alarmes(idAlarme,descricao_alarme, idRegisto) VALUES (@maxidAlarme,"temperatura inserida superior ao normal",New.idRegisto);
end if; 

end $$;

DELIMITER  ;

Answer 1

您尝试向表中插入的值超出了允许的 7(七)但预期的 6(六)。

请始终在“插入”查询中包含您要插入的列。

此表中有七列，但其中一列是“自动增量”列，因此应有6(六)个值插入查询。

CREATE TABLE `registos` (
  `data_registo` char(10) NOT NULL,
  `hora_registo` time NOT NULL,
  `idSensor` varchar(8) NOT NULL,
  `Temperatura` char(6) DEFAULT NULL,
  `Humidade` char(6) DEFAULT NULL,
  `pt_orvalho` char(6) DEFAULT NULL,
  `idRegisto` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idRegisto`,`idSensor`,`data_registo`,`hora_registo`),
  KEY `fk_registos_sensores1_idx` (`idSensor`),
  CONSTRAINT `fk_registos_sensores1

这是“插入”查询:

insert into registos values ('2014-03-31', '14:03:32', 'BrgTH032', '22.3', '45.3', '9.9', '32'); 

有七个个值，但您希望查询看起来像这样(添加了列):

insert into registos (data_registo, hora_registo, idSensor, Temperatura, Humidade, pt_orvalho)
values ('2014-03-31', '14:03:32', 'BrgTH032', '22.3', '45.3', '9.9', '32');

我建议查询应该是:

insert into registos (data_registo, hora_registo, idSensor, Temperatura, Humidade, pt_orvalho)
values ('2014-03-31', '14:03:32', 'BrgTH032', '22.3', '45.3', '9.9');

“registos”上的触发器必须是插入后触发器才能获取新的“idRegisto”值。

create TRIGGER alerta
AFTER INSERT ON registos
FOR EACH ROW
begin