Java MySQLIntegrityConstraintViolationException：无法添加或更新子行：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23973362/
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
MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails
提问by jaredsmith
I have probed numerous questions and documentation on this error and cannot seem to find where I am going wrong. The error is on this line:
我已经探讨了有关此错误的许多问题和文档，但似乎无法找到我出错的地方。错误在这一行：
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Cannot add or update a child row: a foreign key constraint fails (`flutweets`.`refs`,
CONSTRAINT `refs_ibfk_1` FOREIGN KEY (`id`) REFERENCES `tweets` (`id`))
The database is MySQL, and I am using Workbench to inspect it; I also know that all the database engines are InnoDB and the Row Formats are Compact. I have three tables: tweets, hashtags, and references. Here is where I create the tables:
数据库是MySQL，我用Workbench检查；我也知道所有的数据库引擎都是 InnoDB 并且行格式是 Compact。我有三个表：推文、主题标签和参考。这是我创建表的地方：
tweets Table
Statement stmt = null;
try {
stmt = connection.createStatement();
stmt.executeUpdate("CREATE TABLE " + TWEETS_TABLE + "("
+ TWEETS_ID + " BIGINT NOT NULL AUTO_INCREMENT,"
+ TWEETS_TIME + " VARCHAR(255),"
+ TWEETS_USER + " VARCHAR(255),"
+ TWEETS_TEXT + " VARCHAR(255) NOT NULL,"
+ TWEETS_LOCATION + " VARCHAR(255),"
+ TWEETS_CITY + " VARCHAR(255),"
+ TWEETS_PROVINCE + " VARCHAR(255),"
+ TWEETS_COUNTRY + " VARCHAR(255),"
+ TWEETS_TIME_ZONE + " VARCHAR(255),"
+ TWEETS_LATITUDE + " DECIMAL(20,16),"
+ TWEETS_LONGITUDE + " DECIMAL(20,16),"
+ "PRIMARY KEY (" + TWEETS_ID + ")"
+ ")");
. . .
hashtags Table
Statement stmt = null;
try {
stmt = connection.createStatement();
stmt.executeUpdate("CREATE TABLE " + HASHTAG_TABLE + "("
+ HASHTAG_ID + " BIGINT NOT NULL AUTO_INCREMENT,"
+ HASHTAG_TEXT + " VARCHAR(255),"
+ TWEETS_ID + " BIGINT NOT NULL,"
+ "PRIMARY KEY (" + HASHTAG_ID + "),"
+ "FOREIGN KEY (" + TWEETS_ID + ")"
+ "REFERENCES " + TWEETS_TABLE
+ "(" + TWEETS_ID + ")"
+ ")");
. . .
references Table
Statement stmt = null;
try {
stmt = connection.createStatement();
stmt.executeUpdate("CREATE TABLE " + REF_TABLE + "("
+ REF_ID + " BIGINT NOT NULL AUTO_INCREMENT,"
+ REF_TEXT + " VARCHAR(255),"
+ TWEETS_ID + " BIGINT NOT NULL,"
+ "PRIMARY KEY (" + REF_ID + "),"
+ "FOREIGN KEY (" + TWEETS_ID + ")"
+ "REFERENCES " + TWEETS_TABLE
+ "(" + TWEETS_ID + ")"
+ ")");
Here are the statements I use to insert and the methods to insert them (I am using prepared statements):
以下是我用来插入的语句和插入它们的方法（我使用的是准备好的语句）：
/* INITIALIZE PREPARED STATEMENTS */
// Tweets
private static final String TWEETS_INSERT = "INSERT INTO " + TWEETS_TABLE
+ " (" + TWEETS_TIME + ", " + TWEETS_USER + ", " + TWEETS_TEXT + ", " + TWEETS_LOCATION + ", " + TWEETS_CITY + ", " + TWEETS_PROVINCE + ", "
+ TWEETS_COUNTRY + ", " + TWEETS_TIME_ZONE + ", " + TWEETS_LATITUDE + ", " + TWEETS_LONGITUDE + ")"
+ " VALUES(?,?,?,?,?,?,?,?,?,?)";
private PreparedStatement tweetsStatement;
// Hashtags
private static final String HASHTAGS_INSERT = "INSERT INTO " + HASHTAG_TABLE
+ " (" + HASHTAG_TEXT + ", " + TWEETS_ID + ")" + " VALUES(?,?)";
private PreparedStatement hashStatement;
// References
private static final String REF_INSERT = "INSERT INTO " + REF_TABLE
+ " (" + REF_TEXT + ", " + TWEETS_ID + ")" + " VALUES(?,?)";
private PreparedStatement refStatement;
public void insertHashtag(FluTweet tweet, int hashIndex, Integer tweetID) {
public void insertHashtag(FluTweet tweet, int hashIndex, Integer tweetID) {
// Pull all of the Hashtag's relevant elements.
String hashText = tweet.getHashtag(hashIndex);
try {
hashStatement.setString(1, hashText);
hashStatement.setString(2, tweetID.toString());
//INSERT HASHTAG INTO TABLE
hashStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void insertReference(FluTweet tweet, int referenceIndex, Integer tweetID) {
// Pull all of the Reference's relevant elements.
String refText = tweet.getReference(referenceIndex);
try {
refStatement.setString(1, refText);
refStatement.setString(2, tweetID.toString());
//INSERT REFERENCE INTO TABLE
refStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
I have no idea why this is not working, any help would be much appreciated. Like I said, I have probed numerous other posts and cannot find relevant help.
我不知道为什么这不起作用，任何帮助将不胜感激。就像我说的，我已经探索了许多其他帖子，但找不到相关帮助。
回答by jaredsmith
Background
背景
I found out the answer to my own question. It was never a problem with any of the actual database handling. It turns out, when I was passing the id to be used as the foreign id in hashtags and references, I was getting my id from a counter in the main class; where every new tweet had a number that went with each hashtag and reference for that tweet that would later be used as a foreign key. That foreign key would reference the primary key in the tweets table, which auto increments.
我找到了我自己问题的答案。任何实际的数据库处理从来都不是问题。事实证明，当我传递 id 用作主题标签和引用中的外来 id 时，我是从主类中的计数器获取我的 id；其中每条新推文都有一个数字，与每个主题标签和该推文的引用一起使用，稍后将用作外键。该外键将引用推文表中的主键，该主键会自动递增。
Here was the problem:
这是问题：
It turns out that MySQL is NOTzero-indexed when you auto-increment; where the first value is zero, then one, then two, etc. Instead, it starts at one. So when I tried to reference the primary key with the foreign key, the counter that started at zero for every tweet that I assigned (the foreign key) was being incorrectly matched to the auto incrementing tweet id in the tweets table (primary key).
事实证明，当您自动递增时，MySQL不是零索引的；其中第一个值是零，然后是一，然后是二，等等。相反，它从一开始。因此，当我尝试使用外键引用主键时，为我分配的每条推文（外键）从零开始的计数器与推文表中的自动递增推文 ID（主键）不正确匹配。
Therefore, I was always off by one.
所以，我总是差一分。
I hope this helps anyone that may have a similar issue.
我希望这可以帮助任何可能有类似问题的人。
回答by Satyanarayana.Ruppa
if we didn't set valid child class object primary key value then we can see the MySQLIntegrityConstraintViolationException.if we ignore the primary key value of child class object then based on the configurations set in hibernate pojo/hbm.xml files first level cache(session) will flush it into data base.
如果我们没有设置有效的子类对象主键值，那么我们可以看到MySQLIntegrityConstraintViolationException 。如果我们忽略子类对象的主键值，那么基于 hibernate pojo/hbm.xml 文件中设置的配置一级缓存（会话) 将其刷新到数据库中。
