SQLSTATE [23000]: Integrity constraint violation: 1452 Unable to add or update child row: foreign key constraint failed - Laravel

Question

I know this question has already been asked, but I still cannot find what I am doing wrong.

I am using the Laravel framework.

I have 2 tables (users and locations). When I want to create a user, I get an error message:

SQLSTATE [23000]: Violation of integrity constraint: 1452 Unable to add or update child row: foreign key constraint not fulfilled ( festival_aid . users , CONSTRAINT fk_users_locations1 FOREIGN KEY ( location_id ) LINKS locations ( location_id ) ON DELETE CASCADE ON UPDATE NO ACTION) (SQL: insert values ​​(?,?,?) into users ( user_id , user_email , location_id )) (Bindings: array (0 => '1', 1 => ' [e-mail] ', 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 layout

 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'); }); } 

User model

 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 cannot find what I am doing wrong. Obviously, something is wrong with the foreign key.

Answer 1

User string requires a link to a valid location. This location must be accessible before the user is saved. So, first save the location, and then save the user and uncomment the line $user->location_id , and you're done.

Answer 2

Those developers who encounter this error:

SQLSTATE [23000]: Integrity constraint violation: 1452 Unable to add or update child row:
foreign key failure ( laravel . articles , CONSTRAINT articles_user_id_foreign FOREIGN KEY ( user_id ) LINKS users ( id ) ON DELETE CASCADE)
(SQL: paste in the values ​​of articles ( title , user_id , body , updated_at , created_at )) (rao, 1, sflkkjk, 2016-03-01 20:45:32, 2016-03-01 20:45:32))
SQLSTATE [23000]: Integrity constraint violation: 1452 Unable to add or update child row:
foreign key failure ( laravel . articles , CONSTRAINT articles_user_id_foreign FOREIGN KEY ( user_id ) LINKS users ( id ) ON DELETE CASCADE)
(SQL: paste in the values ​​of articles ( title , user_id , body , updated_at , created_at )) (rao, 1, sflkkjk, 2016-03-01 20:45:32, 2016-03-01 20:45:32))

Save the user user record first, then try to insert the record, and then easily insert into the database.

KEY POINT

Two tables: one is an article and the other is a user. The user has many articles, but no article has many users. Thus, the foreign key is shifted towards the article table. If there is no entry in the user table, you encounter the same error as before.

By doing this, we can easily solve this problem.