CODEWITHSUNDEEP
phpmysqllaravel
petrushka_95
petrushka_95

Reputation: 79

LARAVEL:MYSQL : Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

I am trying to apply a foreign Key to column user_id, related with ID of table users

I have tried doing it by migrations and in workbench and this error keeps coming:

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

this is the mysql:

ALTER TABLE `dh_booky`.`books` 

ADD CONSTRAINT `books_user_id_foreign`

  FOREIGN KEY (`user_id`)

  REFERENCES `dh_booky`.`users` (`id`)

  ON DELETE NO ACTION

  ON UPDATE NO ACTION;

1st migration books:

 public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('title_id')->unsigned();
            $table->foreign('title_id')->references('id')->on('titles');
            $table->bigInteger('author_id')->unsigned();
            $table->foreign('author_id')->references('id')->on('authors');
            $table->string('image')->nullable();

        });
    }

Update migration books: 

class UpdateBooksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
        $table->bigInteger('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');
        //php artisan make:migration update_votes_table



});

User migration:

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps();
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
        });
    }

Upvotes: 1

Views: 1378

Answers (3)

Watercayman
Watercayman

Reputation: 8168

Set the user migration to earlier, before the books table migration happens. Otherwise, there is no user table in existence yet to reference when creating the books table, and thus the error.

This one first:

Schema::create('users', function (Blueprint $table) {...}

Then when you create the books table, the foreign key to users will have a table to connect to.

Upvotes: 1

Lijesh Shakya
Lijesh Shakya

Reputation: 2542

Seems like you are updating users table instead of books table in UpdateBooksTable migration.

You might already have data on the books table. Because of which, the existing books does not reference the user_id and it is causing the error.

You can add nullable() to the user_id so that existing books have user_id null.

class UpdateBooksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        //Seem you are updating books table. change users to books 
        Schema::table('users', function (Blueprint $table) {
            $table->bigInteger('user_id')->unsigned()->nullable();
            $table->foreign('user_id')->references('id')->on('users');
        });
    }
}

Upvotes: 3

Mesuti
Mesuti

Reputation: 908

Migration file code should be below:

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

It can be produce SQL like this:

ALTER TABLE books

ADD CONSTRAINT books_user_id_foreign

FOREIGN KEY fk_user_id(user_id)

REFERENCES users (id)

ON DELETE NO ACTION

ON UPDATE NO ACTION;

Upvotes: 0

Related Questions