SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails - Laravel
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_id
line and you are done.
Gilko
Updated on December 20, 2020Comments
-
Gilko over 3 years
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.
I have 2 tables (Users and Locations). When I want to create a User, I get the error message:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (
festival_aid
.users
, CONSTRAINTfk_users_locations1
FOREIGN KEY (location_id
) REFERENCESlocations
(location_id
) ON DELETE CASCADE ON UPDATE NO ACTION) (SQL: insert intousers
(user_id
,user_email
,location_id
) values (?, ?, ?)) (Bindings: array ( 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.
-
Sergey Romanov over 9 yearsthat is strange but in my case I do have rows in constraint table before inserting but it still throw this error.