can't update a child row

Question

I created the lessons table long time before. now i would like to add a new user_id column for table lessons,which refer the id on users table.

first i created the add_profile_to_lessons migration file and added the code to create a new column.

The lessons table

if ( !Schema::hasTable('lessons') ) {
    Schema::create('lessons', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('course_id')->unsigned()->nullable();
        $table->foreign('course_id', '54419_596eedbb6686e')->references('id')->on('courses')->onDelete('cascade');
        $table->string('title')->nullable();
        $table->string('slug')->nullable();
        $table->string('lesson_image')->nullable();
        $table->text('short_text')->nullable();
        $table->text('full_text')->nullable();
        $table->integer('position')->nullable()->unsigned();
        $table->tinyInteger('free_lesson')->nullable()->default(0);
        $table->tinyInteger('published')->nullable()->default(0);

        $table->timestamps();
        $table->softDeletes();

        $table->index(['deleted_at']);
    });
}

The users table

if ( !Schema::hasTable('users') ) {
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('email');
        $table->string('password');
        $table->string('remember_token')->nullable();

        $table->timestamps();

    });
}

The schema i created to add a new column in add_profile_to_lessons migration file

Schema::table('lessons', function (Blueprint $table) {
    $table->unsignedInteger('user_id')->after('course_id');
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

When i executed php artisan migrate command, i get this error

[Illuminate\Database\QueryException] SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (quicklms.#sql-d94_102, CONSTR AINT lessons_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE) (SQL: alter table lessons add constraint lessons_u ser_id_foreign foreign key (user_id) references users (id) on delete cascade)

you have your foreign key to not be null, what value should it be using for `user_id` for all the existing 'lessons' records? — lagbox, Nov 12 '19 at 07:37
If there are some rows in lessons table already you have to either create the user_id as nullable, set it's default value to some existing id of user or set the value of user_id for each row before creating a foreign key. If you don't do that, the user_id is filled with 0 for existing rows and that id doesn't exist in user table so foreign key constraint cannot be created. — Michal Hynčica, Nov 12 '19 at 07:40
https://stackoverflow.com/questions/47266089/laravel-migration-integrity-constraint-violation-1452-cannot-add-or-update-a/51474101#51474101 — Roman Meyer, Nov 12 '19 at 08:04
Does this answer your question? [Laravel migration - Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails](https://stackoverflow.com/questions/47266089/laravel-migration-integrity-constraint-violation-1452-cannot-add-or-update-a) — Roman Meyer, Nov 12 '19 at 08:04

score 0 · Answer 1 · answered Nov 12 '19 at 08:42

I solved the problem. What happens is when i executed the command i have seen the user_id column was created and it assigns all the values as zero.

so first i change the user_id value zero to some existing values in the users table. Then i place the below code in add_profile_to_lessons migration file and excecuted the command php artisan migrate and it works ^_^

Schema::table('lessons', function (Blueprint $table) {
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        });

can't update a child row

1 Answers1