MySQL 违反完整性约束：1452 无法添加或更新子行：
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14063652/
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
Integrity constraint violation: 1452 Cannot add or update a child row:
提问by numerical25
I am trying to insert values into my comments table and I am getting a error. Its saying that I can not add or update child row and I have no idea what that means.
我正在尝试将值插入到我的评论表中，但出现错误。它说我无法添加或更新子行，我不知道这意味着什么。
my schema looks something like this
我的架构看起来像这样
-- ----------------------------
-- Table structure for `comments`
-- ----------------------------
DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
`id` varchar(36) NOT NULL,
`project_id` varchar(36) NOT NULL,
`user_id` varchar(36) NOT NULL,
`task_id` varchar(36) NOT NULL,
`data_type_id` varchar(36) NOT NULL,
`data_path` varchar(255) DEFAULT NULL,
`message` longtext,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_comments_users` (`user_id`),
KEY `fk_comments_projects1` (`project_id`),
KEY `fk_comments_data_types1` (`data_type_id`),
CONSTRAINT `fk_comments_data_types1` FOREIGN KEY (`data_type_id`) REFERENCES `data_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_comments_projects1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_comments_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf32;
-- ----------------------------
-- Records of comments
-- ----------------------------
-- ----------------------------
-- Table structure for `projects`
-- ----------------------------
DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
`id` varchar(36) NOT NULL,
`user_id` varchar(36) NOT NULL,
`title` varchar(45) DEFAULT NULL,
`description` longtext,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_projects_users1` (`user_id`),
CONSTRAINT `fk_projects_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf32;
-- ----------------------------
-- Records of projects
-- ----------------------------
INSERT INTO `projects` VALUES ('50dcbc72-3410-4596-8b71-0e80ae7aaee3', '50dcbc5c-d684-40bf-9715-0becae7aaee3', 'Brand New Project', 'This is a brand new project', '2012-12-27 15:24:02', '2012-12-27 15:24:02');
and the mysql statement I am trying to do looks something like this
我试图做的 mysql 语句看起来像这样
INSERT INTO `anthonyl_fbpj`.`comments` (`project_id`, `user_id`, `task_id`, `data_type_id`, `message`, `modified`, `created`, `id`)
VALUES ('50dc845a-83e4-4db3-8705-5432ae7aaee3', '50dcbc5c-d684-40bf-9715-0becae7aaee3', '1', '50d32e5c-abdc-491a-a0ef-25d84e9f49a8', 'this is a test', '2012-12-27 19:20:46', '2012-12-27 19:20:46', '50dcf3ee-8bf4-4685-aa45-4eb4ae7aaee3')
the error I get looks like this
我得到的错误看起来像这样
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (
anthonyl_fbpj.comments, CONSTRAINTfk_comments_projects1FOREIGN KEY (project_id) REFERENCESprojects(id) ON DELETE NO ACTION ON UPDATE NO ACTION)
SQLSTATE[23000]：完整性约束违规：1452 无法添加或更新子行：外键约束失败（
anthonyl_fbpj.comments, CONSTRAINTfk_comments_projects1FOREIGN KEY (project_id) REFERENCESprojects(id) ON DELETE NO ACTION ON UPDATE NO ACTION）
回答by John Woo
It just simply means that the value for column project_idon table commentsyou are inserting doesn't exist on table projects. Bear in mind that the values of column project_idon table commentsis dependent on the values of IDon table Projects.
这只是意味着您要插入的project_idtable 上column 的值在 tablecomments上不存在projects。请记住，列的值project_id上表comments是依赖于值ID表Projects。
The value 50dc845a-83e4-4db3-8705-5432ae7aaee3you are inserting for column project_iddoes not exist on table projects.
50dc845a-83e4-4db3-8705-5432ae7aaee3您为 column 插入的值project_id在 table 上不存在projects。
回答by Jyothu
Make sure you have project_idin the fillableproperty of your Commentmodel.
确保您拥有模型project_id的fillable属性Comment。
I had the same issue, And this was the reason.
我有同样的问题，这就是原因。
回答by tyan
Also make sure that the foreign key you add is the same type of the original column, if the column you're reference is not the same type it will fail too.
还要确保您添加的外键与原始列的类型相同，如果您引用的列不是相同的类型，它也会失败。
回答by narko
In case someone is using Laravel and is getting this problem. I was getting this as well and the issue was in the order in which I was inserting the ids (i.e., the foreign keys) in the pivot table.
如果有人正在使用 Laravel 并遇到此问题。我也遇到了这个问题，问题在于我在数据透视表中插入 ID（即外键）的顺序。
To be concrete, find below an example for a many to many relationship:
具体来说，请在下面找到多对多关系的示例：
wordtokens <-> wordtoken_wordchunk <-> wordchunks
wordtokens <-> wordtoken_wordchunk <-> wordchunks
// wordtoken_wordchunk table
Schema::create('wordtoken_wordchunk', function(Blueprint $table) {
$table->integer('wordtoken_id')->unsigned();
$table->integer('wordchunk_id')->unsigned();
$table->foreign('wordtoken_id')->references('id')->on('word_tokens')->onDelete('cascade');
$table->foreign('wordchunk_id')->references('id')->on('wordchunks')->onDelete('cascade');
$table->primary(['wordtoken_id', 'wordchunk_id']);
});
// wordchunks table
Schema::create('wordchunks', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
$table->string('text');
});
// wordtokens table
Schema::create('word_tokens', function (Blueprint $table) {
$table->increments('id');
$table->string('text');
});
Now my models look like follows:
现在我的模型如下所示：
class WordToken extends Model
{
public function wordchunks() {
return $this->belongsToMany('App\Wordchunk');
}
}
class Wordchunk extends Model
{
public function wordTokens() {
return $this->belongsToMany('App\WordToken', 'wordtoken_wordchunk', 'wordchunk_id', 'wordtoken_id');
}
}
I fixed the problem by exchanging the order of 'wordchunk_id' and 'wordtoken_id' in the Wordchunk model.
我通过交换 Wordchunk 模型中 'wordchunk_id' 和 'wordtoken_id' 的顺序解决了这个问题。
For code completion, this is how I persist the models:
对于代码完成，这就是我坚持模型的方式：
private function persistChunks($chunks) {
foreach ($chunks as $chunk) {
$model = new Wordchunk();
$model->text = implode(' ', array_map(function($token) {return $token->text;}, $chunk));
$tokenIds = array_map(function($token) {return $token->id;}, $chunk);
$model->save();
$model->wordTokens()->attach($tokenIds);
}
}
回答by Sreerag AS
First delete the constraint "fk_comments_projects1" and also its index. After that recreate it.
首先删除约束“fk_comments_projects1”及其索引。之后重新创建它。
回答by Aleksergio
I hope my decision will help. I had a similar error in Laravel. I added a foreign key to the wrong table.
Wrong code:
我希望我的决定会有所帮助。我在 Laravel 中也有类似的错误。我在错误的表中添加了外键。
错误代码：
Schema::create('comments', function (Blueprint $table) {
$table->unsignedBigInteger('post_id')->index()->nullable();
...
$table->foreign('post_id')->references('id')->on('comments')->onDelete('cascade');
});
Schema::create('posts', function (Blueprint $table) {
$table->bigIncrements('id');
...
});
Please note to the function on('comments') above. Correct code
请注意上面的 on('comments') 函数。正确的代码
$table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
回答by Prafulla Kumar Sahu
If you are adding new foreign key to an existing table and the columns are not null and not assigned default value, you will get this error,
如果您向现有表添加新外键并且列不为空且未分配默认值，您将收到此错误，
Either you need to make it nullableor assign default value, or delete all the existing recordsto solve it.
要么你需要使它nullable或者assign default value，或者delete all the existing records解决它。
回答by Mark L?we
I had this issue when I was accidentally using the WRONG "uuid" in my child record. When that happens the constraint looks from the child to the parent record to ensure that the link is correct. I was generating it manually, when I had already rigged my Model to do it automatically. So my fix was:
当我不小心在我的子记录中使用了错误的“uuid”时，我遇到了这个问题。当这种情况发生时，约束会从子记录到父记录，以确保链接正确。当我已经操纵我的模型来自动生成它时，我是手动生成的。所以我的解决方法是：
$parent = Parent:create($recData); // asssigning autogenerated uuid into $parent
Then when I called my child class to insert children, I passed this var value:
然后当我调用我的子类来插入子类时，我传递了这个 var 值：
$parent->uuid
Hope that helps.
希望有帮助。
回答by Antonio Figueiredo Tamura
Maybe you have some rows in the table that you want to create de FK.
也许您想要创建 de FK 表中的一些行。
Run the migration with foreign_key_checks OFF Insert only those records that have corresponding id field in contents table.
使用 foreign_key_checks OFF 运行迁移仅插入那些在内容表中具有相应 id 字段的记录。
