February, 2021

Cannot add foreign key constraint

The most common error I see when trying to create foreign key constraints is you forget that the colmuns must have the same type, including unsigned() if unsigned.

In laravel 5.8 and forward, the auto-generated column type for primary kets is bigIncrements(‘id’). So, if you refer a foreign key constraint, your user_id column needs to be unsignedBigInteger(‘user_id’).

create_users_table.php

public function up()
{
    Schema::create('users', function (Blueprint $table) {

        $table->bigIncrements('id');
        ....
    });
}

create_role_user_pivot_table.php

public function up()
{
    Schema::create('role_user', function (Blueprint $table) {

        // this line throw QueryException "SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint..."
        $table->integer('user_id')->unsigned()->index();

        // this is working
        $table->bigInteger('user_id')->unsigned()->index(); 
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });
}

← → Top