sql = "insert into tb_capacity_weight_source values(null,?,?,?)";
PreparedStatement preStmt = conn.prepareStatement(sql);
preStmt.setInt(1, i);
preStmt.setDouble(2,adjustWeight[count]);
preStmt.setInt(3, result_id);
preStmt.executeUpdate();
报错
Cannot add or update a child row: a foreign key constraint fails (`analysis_database/tb_capacity_weight_source`, CONSTRAINT `FK_REFERENCE_10` FOREIGN KEY (`INDEX_ID`) REFERENCES `tb_capacity_result_index` (`INDEX_ID`)但是代码中要插入的记录,检查过对应的外键INDEX_ID已经存在的。之前写的代码,插入记录中没有INDEX_ID这一项的话是可以顺利插入表中的,增加这个外键之后出现了问题。
不知道是不是与`tb_capacity_result_index` (`INDEX_ID`)多次用作外键有关? 不懂,求解决。
-- ----------------------------
-- Table structure for `tb_capacity_result`
-- ----------------------------
DROP TABLE IF EXISTS `tb_capacity_result`;
CREATE TABLE `tb_capacity_result` (
`RESULT_ID` int(11) NOT NULL auto_increment,
`CAPACITY_ID` int(11) default NULL,
`INDEX_ID` int(11) default NULL,
`RESULT` double default NULL,
PRIMARY KEY (`RESULT_ID`),
KEY `FK_REFERENCE_7` (`CAPACITY_ID`),
KEY `FK_REFERENCE_8` (`INDEX_ID`),
CONSTRAINT `FK_REFERENCE_7` FOREIGN KEY (`CAPACITY_ID`) REFERENCES `tb_capacity` (`CAPACITY_ID`),
CONSTRAINT `FK_REFERENCE_8` FOREIGN KEY (`INDEX_ID`) REFERENCES `tb_capacity_result_index` (`INDEX_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `tb_capacity_source`
-- ----------------------------
DROP TABLE IF EXISTS `tb_capacity_source`;
CREATE TABLE `tb_capacity_source` (
`CAPACITY_SOURCE_ID` int(11) NOT NULL auto_increment,
`CAPACITY_SOURCE` double default NULL,
`CAPACITY_ID` int(11) default NULL,
`INDEX_ID` int(11) default NULL,
PRIMARY KEY (`CAPACITY_SOURCE_ID`),
KEY `FK_REFERENCE_11` (`INDEX_ID`),
KEY `FK_REFERENCE_2` (`CAPACITY_ID`),
CONSTRAINT `FK_REFERENCE_11` FOREIGN KEY (`INDEX_ID`) REFERENCES `tb_capacity_result_index` (`INDEX_ID`),
CONSTRAINT `FK_REFERENCE_2` FOREIGN KEY (`CAPACITY_ID`) REFERENCES `tb_capacity` (`CAPACITY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb_capacity_source
-- ------------------------------ ----------------------------
-- Table structure for `tb_capacity_weight_source`
-- ----------------------------
DROP TABLE IF EXISTS `tb_capacity_weight_source`;
CREATE TABLE `tb_capacity_weight_source` (
`CAPACITY_WEIGHT_SOURCE_ID` int(11) NOT NULL auto_increment,
`CAPACITY_ID` int(11) default NULL,
`INDEX_ID` int(11) default NULL,
`CAPACITY_WEIGHT_SOURCE` double default NULL,
PRIMARY KEY (`CAPACITY_WEIGHT_SOURCE_ID`),
KEY `FK_REFERENCE_1` (`CAPACITY_ID`),
KEY `FK_REFERENCE_10` (`INDEX_ID`),
CONSTRAINT `FK_REFERENCE_1` FOREIGN KEY (`CAPACITY_ID`) REFERENCES `tb_capacity` (`CAPACITY_ID`),
CONSTRAINT `FK_REFERENCE_10` FOREIGN KEY (`INDEX_ID`) REFERENCES `tb_capacity_result_index` (`INDEX_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;
PreparedStatement preStmt = conn.prepareStatement(sql);
preStmt.setInt(1, i);
preStmt.setDouble(2,adjustWeight[count]);
preStmt.setInt(3, result_id);
preStmt.executeUpdate();
报错
Cannot add or update a child row: a foreign key constraint fails (`analysis_database/tb_capacity_weight_source`, CONSTRAINT `FK_REFERENCE_10` FOREIGN KEY (`INDEX_ID`) REFERENCES `tb_capacity_result_index` (`INDEX_ID`)但是代码中要插入的记录,检查过对应的外键INDEX_ID已经存在的。之前写的代码,插入记录中没有INDEX_ID这一项的话是可以顺利插入表中的,增加这个外键之后出现了问题。
不知道是不是与`tb_capacity_result_index` (`INDEX_ID`)多次用作外键有关? 不懂,求解决。
-- ----------------------------
-- Table structure for `tb_capacity_result`
-- ----------------------------
DROP TABLE IF EXISTS `tb_capacity_result`;
CREATE TABLE `tb_capacity_result` (
`RESULT_ID` int(11) NOT NULL auto_increment,
`CAPACITY_ID` int(11) default NULL,
`INDEX_ID` int(11) default NULL,
`RESULT` double default NULL,
PRIMARY KEY (`RESULT_ID`),
KEY `FK_REFERENCE_7` (`CAPACITY_ID`),
KEY `FK_REFERENCE_8` (`INDEX_ID`),
CONSTRAINT `FK_REFERENCE_7` FOREIGN KEY (`CAPACITY_ID`) REFERENCES `tb_capacity` (`CAPACITY_ID`),
CONSTRAINT `FK_REFERENCE_8` FOREIGN KEY (`INDEX_ID`) REFERENCES `tb_capacity_result_index` (`INDEX_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `tb_capacity_source`
-- ----------------------------
DROP TABLE IF EXISTS `tb_capacity_source`;
CREATE TABLE `tb_capacity_source` (
`CAPACITY_SOURCE_ID` int(11) NOT NULL auto_increment,
`CAPACITY_SOURCE` double default NULL,
`CAPACITY_ID` int(11) default NULL,
`INDEX_ID` int(11) default NULL,
PRIMARY KEY (`CAPACITY_SOURCE_ID`),
KEY `FK_REFERENCE_11` (`INDEX_ID`),
KEY `FK_REFERENCE_2` (`CAPACITY_ID`),
CONSTRAINT `FK_REFERENCE_11` FOREIGN KEY (`INDEX_ID`) REFERENCES `tb_capacity_result_index` (`INDEX_ID`),
CONSTRAINT `FK_REFERENCE_2` FOREIGN KEY (`CAPACITY_ID`) REFERENCES `tb_capacity` (`CAPACITY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb_capacity_source
-- ------------------------------ ----------------------------
-- Table structure for `tb_capacity_weight_source`
-- ----------------------------
DROP TABLE IF EXISTS `tb_capacity_weight_source`;
CREATE TABLE `tb_capacity_weight_source` (
`CAPACITY_WEIGHT_SOURCE_ID` int(11) NOT NULL auto_increment,
`CAPACITY_ID` int(11) default NULL,
`INDEX_ID` int(11) default NULL,
`CAPACITY_WEIGHT_SOURCE` double default NULL,
PRIMARY KEY (`CAPACITY_WEIGHT_SOURCE_ID`),
KEY `FK_REFERENCE_1` (`CAPACITY_ID`),
KEY `FK_REFERENCE_10` (`INDEX_ID`),
CONSTRAINT `FK_REFERENCE_1` FOREIGN KEY (`CAPACITY_ID`) REFERENCES `tb_capacity` (`CAPACITY_ID`),
CONSTRAINT `FK_REFERENCE_10` FOREIGN KEY (`INDEX_ID`) REFERENCES `tb_capacity_result_index` (`INDEX_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;
表中已经有对应的项,
而且同样的INDEX_ID,可以插入新纪录到`tb_capacity_source` ,但是插入纪录到`tb_capacity_weight_source`就不行
先建两张表:user和card,为了简单,都只有一个字段:id,让他们彼此成为对方的外键:
mysql> create table user(id int primary key) character set utf8;
mysql> create table card(id int primary key, constraint fk_user_id foreign key (id) references user(id))character set utf8;
mysql> alter table user add constraint fk_card_id foreign key(id) references card(id);
此时,任何一张表都无法插入数据,也不能删除表。
mysql> drop table user;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
解决方法还是要删除外键约束,先通过:show create table user;找到外键约束的名字,再把约束删除:
mysql> alter table user drop foreign key fk_card_id;
此时就可以向user插入数据了,card表也就可以删除了。这个例子比较无聊,只是列出来,希望对遇到此问题的朋友有帮助。