php SQLSTATE[23000]：违反完整性约束：1452 无法添加或更新子行：外键约束失败 - Laravel

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails - Laravel

提问by Gilko

I know that this question has already been asked, but I still can't find what I'm doing wrong.

我知道这个问题已经有人问过了，但我仍然找不到我做错了什么。

I'm using the framework Laravel.

我正在使用框架 Laravel。

I have 2 tables (Users and Locations). When I want to create a User, I get the error message:

我有 2 个表（用户和位置）。当我想创建一个用户时，我收到错误消息：

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (festival_aid.users, CONSTRAINT fk_users_locations1FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE CASCADE ON UPDATE NO ACTION) (SQL: insert into users(user_id, user_email, location_id) values (?, ?, ?)) (Bindings: array ( 0 => '1', 1 => '[email protected]', 2 => '1', ))

SQLSTATE[23000]: 完整性约束违规：1452 无法添加或更新子行：外键约束失败 ( festival_aid. users, CONSTRAINT fk_users_locations1FOREIGN KEY ( location_id) REFERENCES locations( location_id) ON DELETE CASCADE ON UPDATE NO ACTION) (SQL: insert into users( user_id, user_email, location_id) 值 (?, ?, ?)) (绑定：数组 ( 0 => '1', 1 => '[email protected]', 2 => '1', ))

Table Users

表用户

CREATE TABLE IF NOT EXISTS `festival_aid`.`users` (
  `user_id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_email` VARCHAR(45) NOT NULL,
  `user_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_modified` TIMESTAMP NULL,
  `user_deleted` TIMESTAMP NULL,
  `user_lastlogin` TIMESTAMP NULL,
  `user_locked` TIMESTAMP NULL,
  `location_id` BIGINT NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `user_email_UNIQUE` (`user_email` ASC),
  CONSTRAINT `fk_users_locations1`
    FOREIGN KEY (`location_id`)
    REFERENCES `festival_aid`.`locations` (`location_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
ENGINE = InnoDB;

Table Locations

餐桌位置

DROP TABLE IF EXISTS `festival_aid`.`locations` ;

CREATE TABLE IF NOT EXISTS `festival_aid`.`locations` (
  `location_id` BIGINT NOT NULL AUTO_INCREMENT,
  `location_latitude` FLOAT NOT NULL,
  `location_longitude` FLOAT NOT NULL,
  `location_desc` VARCHAR(255) NULL,
  `location_type` VARCHAR(45) NULL,
  PRIMARY KEY (`location_id`))
ENGINE = InnoDB;

Migration User

迁移用户

public function up()
    {
        Schema::table('users', function(Blueprint $table)
        {
            $table->increments('user_id');
            $table->string('user_email');
            $table->timestamp('user_created');
            $table->timestamp('user_modified');
            $table->timestamp('user_deleted');
            $table->timestamp('user_lastlogin');
            $table->timestamp('user_locked');

            $table->foreign('location_id')
                ->references('id')->on('locations');
            //->onDelete('cascade');
        });
    }

Migration Location

迁移位置

public function up()
    {
        Schema::table('locations', function(Blueprint $table)
        {
            $table->primary('location_id');
            $table->float('location_latitude');
            $table->float('location_longitude');
            $table->string('location_desc');
            $table->string('location_type');
        });
    }

Model User

模型用户

public function location()
    {
        return $this->belongsTo('Location');
    }

Model Location

模型位置

 public function user()
    {
        return $this->hasOne('User');
    }

Controller

控制器

public function store()
    {
        $input = Input::all();

        $rules = array('user_email' => 'required|unique:users|email');

        $v = Validator::make($input, $rules);

        if($v->passes())
        {
            $user = new User();
            $location = new Location();

            $user->user_email = $input['user_email'];
            //$user->location_id = $input['location_id'];

            $location->location_latitude = $input['location_latitude'];
            $location->location_longitude = $input['location_longitude'];

            $user->save();
            $location->save();
    }

I can't seem to find what I'm doing wrong. Obviously there is something wrong with the foreign key.

我似乎无法找到我做错了什么。显然外键有问题。

回答by Haneev

A user row needs a reference to a valid location. That location must be available before the user is saved. So, first save the location and then save the user and uncomment the $user->location_idline and you are done.

用户行需要对有效位置的引用。在保存用户之前，该位置必须可用。因此，首先保存位置，然后保存用户并取消注释该$user->location_id行，您就完成了。

回答by Kaleemullah

Those Developers who face this error:

那些面临此错误的开发人员：

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row:
a foreign key constraint fails (laravel.articles, CONSTRAINT articles_user_id_foreignFOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE)
(SQL: insert into articles(title, user_id, body, updated_at, created_at) values (rao, 1, sflkkjk, 2016-03-01 20:45:32, 2016-03-01 20:45:32))
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row:
a foreign key constraint fails (laravel.articles, CONSTRAINT articles_user_id_foreignFOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE)
(SQL: insert into articles(title, user_id, body, updated_at, created_at) values (rao, 1, sflkkjk, 2016-03-01 20:45:32, 2016-03-01 20:45:32))

SQLSTATE[23000]: 完整性约束违规：1452 无法添加或更新子行：
外键约束失败 ( laravel. articles, CONSTRAINT articles_user_id_foreignFOREIGN KEY ( user_id) REFERENCES users( id) ON DELETE CASCADE)
(SQL: insert into articles( title, user_id, body, updated_at, created_at) values （饶，1，sflkkjk，2016年3月1日20时45分32秒，2016年3月1日20时45分32秒））
SQLSTATE [23000]：完整性约束违规：1452不能添加或更新子行：
外键约束失败 ( laravel. articles, CONSTRAINT articles_user_id_foreignFOREIGN KEY ( user_id) REFERENCES users( id) ON DELETE CASCADE)
(SQL: insert into articles( title, user_id,body, updated_at, created_at) 值 (rao, 1, sflkkjk, 2016-03-01 20:45:32, 2016-03-01 20:45:32))

First Save the user record of user then try to insert record then easily inserted into the database.

首先保存用户的用户记录，然后尝试插入记录，然后轻松插入数据库。

KEY POINT

重点

Two Tables: one is article and other is user. A user has many articles but no article has many users. So foreign key is shifted toward the article table. If user table has no record then you face the same error seen earlier.

两张表：一张是文章，一张是用户。一个用户有很多文章，但没有文章有很多用户。所以外键移向文章表。如果用户表没有记录，那么您将面临之前看到的相同错误。

By doing this we can easily solve this issue.

通过这样做，我们可以轻松解决这个问题。