Laravel migration "Cannot add foreign key constraint" error with MySQL database

Question

I am developing a Laravel application. I am using MySQL for the database. I have created a model class and am trying to run the migration on it. But when, I run migration, I am getting error with adding the foreign key constraint. This is what I have done so far.

First I have migrated the built in Laravel user model running this command.

php artisan migrate

The users table was created in the database.

Then I created another model running this command.

php artisan make:model TodoItem -m

Then I added the following code to the migration file.

class CreateTodoItemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('todo_items', function (Blueprint $table) {
            $table->text('about');
            $table->integer('user_id');
            $table->increments('id');
            $table->timestamps();

            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('todo_items');
    }
}

AS you can see above, I am building the relationship between users table and todo_items table by adding a foreign key to the todo_items table. Then, I tried to migrate the TodoItem model by running this command.

php artisan migrate

When I run the command, I got this error.

  Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `todo_items` add constraint `todo_items_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade)

  at /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668| 

 Exception trace:

  1   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  2   PDOStatement::execute()
      /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  Please use the argument -v to see more details.

I did the same in my previous project which is using Postgresql. I was working fine. For this project, I am using MySQL database and now it is giving me the above error.

Answer 1

I had the same problem for a few days. I am not a Laravel developer but as i understood i was trying to create foreign keys for non existing tables. For example i had this table which was first in the migration order 0001_01_01_000006_create_paste_settings_table. This table was referring to the table in the next migration file:

$table->foreign("paste_id")->references("id")->on("pastes")->onUpdate("cascade")->onDelete("cascade");

0001_01_01_000007_create_pastes_table you can see ordering numbers: 6 and 7 in the end of each file name.

The solution might not be the best(i am only starting learning Laravel) but it helped me. I created another migration

artisan make:migration add_foreign_keys_to_existing_table

and there i created foreign keys. I will attach my code as a reference.

add_foreign_keys_to_existing_tables.php content

public function up(): void
{
    Schema::table('paste_tags', function (Blueprint $table) {
        $table->foreign("paste_id")->references("id")->on("pastes")->onUpdate("cascade")->onDelete("cascade");
        $table->foreign("tag_id")->references("id")->on("tags")->onUpdate("cascade")->onDelete("cascade");
    });
    Schema::table('pastes', function (Blueprint $table) {
        $table->foreign("author_id")->references("id")->on("users")->onUpdate("cascade")->onDelete("cascade");

    });
    Schema::table('paste_settings', function (Blueprint $table) {
        $table->foreign("paste_id")->references("id")->on("pastes")->onUpdate("cascade")->onDelete("cascade");
        $table->foreign("category_id")->references("id")->on("paste_categories")->onUpdate("cascade")->onDelete("cascade");
    });


}

You can actualy create all your foreign keys in this file:

Answer 2

try

    $table->unsignedBigInteger('user_id')->nullable()->index();  
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

Answer 3

This is because you added $table->integer('user_id'); to your migration file. You must add an unsignedInteger instead of an integer, because the original id column of the users table is unsigned (and both columns must be exactly the same).

[EDIT]

Since Laravel 5.8, the id column type of the default users table is no longer an integer. It is now a bigInteger.

Answer 4

The issue was that either mysql didn't want foreign keys during table creation, or laravel was issuing them in the wrong order.

In short, this didn't work:

Schema::create('todo_items', function (Blueprint $table) {
    $table->text('about');
    $table->integer('user_id')->unsigned();
    $table->increments('id');
    $table->timestamps();

    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

This worked:

  Schema::create('todo_items', function (Blueprint $table) {
    $table->text('about');
    $table->integer('user_id')->unsigned();
    $table->increments('id');
    $table->timestamps();

});

Schema::table('todo_items',  function(Blueprint $table){

    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

Answer 5

Try running it like this, this should work.

// also, do the unsigned thing the people above me posted

public function up()
{
    Schema::create('todo_items', function (Blueprint $table) {
        $table->text('about');
        $table->integer('user_id');
        $table->increments('id');
        $table->timestamps();

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

Answer 6

Change this

$table->integer('user_id');

To this

$table->unsignedInteger('user_id')->nullable(false);