MySQL 无法添加或更新子行：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5994478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot add or update a child row: a foreign key constraint fails
提问by BioGeek
I have a database with the following schema:
我有一个具有以下架构的数据库：
The following SQL creates the relevant tables:
以下 SQL 创建相关表：
-- -----------------------------------------------------
-- Table `ninikske_bioldb`.`CodingRegion`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ninikske_bioldb`.`CodingRegion` (
`CodRegID` VARCHAR(45) NOT NULL ,
`CD_Start` INT NOT NULL ,
`CD_Stop` INT NOT NULL ,
`ORFs_ORF_ID` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`ORFs_ORF_ID`, `CodRegID`) ,
INDEX `fk_Exons_ORFs1` (`ORFs_ORF_ID` ASC) ,
CONSTRAINT `fk_Exons_ORFs1`
FOREIGN KEY (`ORFs_ORF_ID` )
REFERENCES `ninikske_bioldb`.`ORFs` (`ORF_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `ninikske_bioldb`.`Experiment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ninikske_bioldb`.`Experiment` (
`Probe_GenomicPos` INT NOT NULL ,
`SampleName` VARCHAR(45) NOT NULL ,
`Intensities` FLOAT NOT NULL ,
`ExperimentName` VARCHAR(45) NOT NULL ,
`ProbeID` INT NOT NULL ,
PRIMARY KEY (`Probe_GenomicPos`, `SampleName`, `ProbeID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `ninikske_bioldb`.`CE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ninikske_bioldb`.`CE` (
`OrfId` VARCHAR(45) NOT NULL ,
`CodRegId` VARCHAR(45) NOT NULL ,
`GenPos` INT NOT NULL ,
`ExpSam` VARCHAR(45) NOT NULL ,
`ProbeId` INT NOT NULL ,
PRIMARY KEY (`OrfId`, `CodRegId`, `GenPos`, `ExpSam`, `ProbeId`) ,
INDEX `fk_CodingRegion_has_Experiment_Experiment1` (`GenPos` ASC, `ExpSam` ASC, `ProbeId` ASC) ,
INDEX `fk_CodingRegion_has_Experiment_CodingRegion1` (`OrfId` ASC, `CodRegId` ASC) ,
CONSTRAINT `fk_CodingRegion_has_Experiment_CodingRegion1`
FOREIGN KEY (`OrfId` , `CodRegId` )
REFERENCES `ninikske_bioldb`.`CodingRegion` (`ORFs_ORF_ID` , `CodRegID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_CodingRegion_has_Experiment_Experiment1`
FOREIGN KEY (`GenPos` , `ExpSam` , `ProbeId` )
REFERENCES `ninikske_bioldb`.`Experiment` (`Probe_GenomicPos` , `SampleName` , `ProbeID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
When I execute the following INSERT statement via Java:
当我通过 Java 执行以下 INSERT 语句时：
String query = "INSERT INTO ninikske_bioldb.CE VALUES('" + cs.getORF_ID().getORF_ID() + "','" + cs.getCodRegID().getCodRegID() + "'," + cs.getExp().getProbePos() + ",'" + cs.getExp().getExpName()+ "', " + cs.getExp().getProbeID() + ");";
Statement stmt = con.createStatement();
stmt.executeUpdate(query);
I get the following error:
我收到以下错误：
Cannot add or update a child row: a foreign key constraint fails (`ninikske_bioldb/CE`, CONSTRAINT `fk_CodingRegion_has_Experiment_Experiment1` FOREIGN KEY (`GenPos`, `ExpSam`, `ProbeId`) REFERENCES `Experiment` (`Probe_GenomicPos`, `SampleName`, `ProbeID`) )
I read several other questions about the same error message, but i can't seem to find the insight I need.
我阅读了有关同一错误消息的其他几个问题，但似乎无法找到所需的见解。
EditThe query is:
INSERT INTO ninikske_bioldb.CE VALUES('AT3G01190.1','cd474',67262,'H20', 1709);
编辑查询是：
INSERT INTO ninikske_bioldb.CE VALUES('AT3G01190.1','cd474',67262,'H20', 1709);
When I check I the parent tables, I get following results:
当我检查父表时，我得到以下结果：
mysql> SELECT * FROM CodingRegion WHERE ORFs_ORF_ID = 'AT3G01190.1';
+----------+----------+---------+-------------+
| CodRegID | CD_Start | CD_Stop | ORFs_ORF_ID |
+----------+----------+---------+-------------+
| cd474 | 67243 | 67649 | AT3G01190.1 |
| cd475 | 67733 | 67892 | AT3G01190.1 |
| cd476 | 67991 | 68176 | AT3G01190.1 |
| cd477 | 68272 | 68484 | AT3G01190.1 |
+----------+----------+---------+-------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM CodingRegion WHERE CodRegID = 'cd474';
+----------+----------+---------+-------------+
| CodRegID | CD_Start | CD_Stop | ORFs_ORF_ID |
+----------+----------+---------+-------------+
| cd474 | 67243 | 67649 | AT3G01190.1 |
+----------+----------+---------+-------------+
1 row in set (0.03 sec)
mysql> SELECT * FROM Experiment WHERE Probe_GenomicPos = '67262';
+------------------+--------------+-------------+----------------+---------+
| Probe_GenomicPos | SampleName | Intensities | ExperimentName | ProbeID |
+------------------+--------------+-------------+----------------+---------+
| 67262 | 1signalpart1 | 8.94432 | H20 | 1709 |
+------------------+--------------+-------------+----------------+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Experiment WHERE SampleName = 'H20'
Empty set (0.00 sec)
mysql> SELECT * FROM Experiment WHERE ProbeID = '1709';
+------------------+--------------+-------------+----------------+---------+
| Probe_GenomicPos | SampleName | Intensities | ExperimentName | ProbeID |
+------------------+--------------+-------------+----------------+---------+
| 67262 | 1signalpart1 | 8.94432 | H20 | 1709 |
+------------------+--------------+-------------+----------------+---------+
1 row in set (0.00 sec)
Thanks in advance.
提前致谢。
回答by Mike Sherrill 'Cat Recall'
The constraint that's failing, "fk_CodingRegion_has_Experiment_Experiment1", requires that the values you enter for these three columns--GenPos, ExpSam, and ProbeId--already exist in the table "ninikske_bioldb.Experiment" in the columns Probe_GenomicPos, SampleName, and ProbeID. (In that order.)
失败的约束“fk_CodingRegion_has_Experiment_Experiment1”要求您为这三列（GenPos、ExpSam 和 ProbeId）输入的值已经存在于表“ninikske_bioldb.Experiment”的 Probe_GenomicPos、SampleName 和 ProbeID 列中。（以该顺序。）
Look in the table ninikske_bioldb.Experiment for those three values.
在 ninikske_bioldb.Experiment 表中查找这三个值。
SELECT *
FROM ninikske_bioldb.Experiment
WHERE Probe_GenomicPos = ?
AND SampleName = '?'
AND ProbeID = ?
It's possible that
有可能
- the primary key of the table Experiment is wrong (should include ExperimentName rather than SampleName), or
- the foreign key reference is wrong (should reference ExperimentName rather than SampleName), or
- the function cs.getExp().getExpName() is the wrong function to use.
- 表 Experiment 的主键错误（应包含 ExperimentName 而不是 SampleName），或
- 外键引用错误（应该引用 ExperimentName 而不是 SampleName），或者
- 函数 cs.getExp().getExpName() 是使用错误的函数。
I think the last one is the most likely. Probably should be getSampleName(), or something like that.
我觉得最后一种可能性最大。可能应该是 getSampleName() 或类似的东西。
