Ravel fails to migrate with SQLSTATE [23000] error when handling foreign key constraints.

users table
https://gyazo.com/831e6d98dfc12e266db09b3a0db6bd11

leectures table
https://gyazo.com/c2626070a76bc14e555d89c7c3d7dfdf

There are two tables above,
I want to associate the user_id column with the user's id column in the
This is how I did it.

add_foreign_to_lectures_table.php

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migration;

classAddForeignToLecturesTable extensions Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('lectures', function(Blueprint$table){
            $table->foreign('user_id')->references('id')->on('users');
        });
    }    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('lectures', function(Blueprint$table){
            $table->dropForeign('lectures_user_id_foreign');
        });
    }
}

However, I received this error.

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`bbl`.`#sql-51e8_25`, CONSTRUCTION `lectures_user_id_foreign` FOREIGN KEY(`user_id`)`FOLD (`FOLDUSER`FOLDER`FOLDERS(`FOLDER))   
   references `users`(`id`))

https://gyazo.com/bf3045cf3582c6084526b01719cdd6ce

I would appreciate it if you could tell me the solution to thism(__)m

php laravel

2022-09-30 21:48

2 Answers

The cause this time is
"Data that does not exist on users.id exists in the selectures.user_id."

Workaround is
Delete or replace "data that exists in users.id in selectures.user_id".

The foreign key constraint is

Only data that exists in the source field can exist in the data in the destination field.
The specification is

In this case

Only data present on users.id can be present in the selectures.user_id
It will be shaped like this.
However, in the first place
"Data that does not exist on users.id exists in the selectures.user_id"
You cannot even set the foreign key.
This error is also a similar message.

Then, please look at the actual data and investigate what kind of data exists.
The way you deal with it also depends on the specifications of the system, so the way you deal with it will change.

Delete inconsistent data or
Add data to users
Replace null in selectures.user_id

I think it will be one of the following.

By the way
Looking at the contents of the data in the comment, it seems that there is a zero in the pictures.user_id.
If rectures.user_id is not required, NULL must be entered instead of 0 or users.id must be 0 as pseudo data.

2022-09-30 21:48

I can manage it somehow.A similar error and answer were written in the past log.

2022-09-30 21:48

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656