Eloquent - Cannot add foreign key constraint

Question

Common question I guess, but I can't resolve it despite the informations I found online.

I've a sequence of hasMany relations :

User has many clients has many contracts has many materials.

I did my best but I find myself confronted to this error :

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: create table clients (id int unsigned not null, user_id int unsigne d null, ...) default character set utf8mb4 collate
utf8mb4_unicode_ci engine = InnoDB)

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Migrations :

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->engine = 'InnoDB';

            // Keys

            $table->increments('id');

            // Other

            ...
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::disableForeignKeyConstraints();
        Schema::dropIfExists('clients');
        Schema::drop('users');
        Schema::enableForeignKeyConstraints();
    }
}

class CreateClientsTable extends Migration
{
    public function up()
    {
        Schema::create('clients', function (Blueprint $table) {
            $table->engine = 'InnoDB';

            // keys

            $table->unsignedInteger('id')->unique();
            $table->primary('id');

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

            // others
            ...
        });
    }

    public function down()
    {
        Schema::disableForeignKeyConstraints();
        Schema::dropIfExists('contracts');
        Schema::drop('clients');
        Schema::enableForeignKeyConstraints();
    }
}

class CreateContractsTable extends Migration
{
    public function up()
    {
        Schema::create('contracts', function (Blueprint $table) {
            $table->engine = 'InnoDB';

            // Keys

            $table->increments('id');
            $table->string('contract_number')->unique();

            $table->unsignedInteger('client_id');
            $table->foreign('client_id')->references('id')->on('clients')
                  ->onDelete('cascade');


            // Others

            ...
        });
    }

    public function down()
    {
        Schema::disableForeignKeyConstraints();
        Schema::dropIfExists('materials');
        Schema::drop('contracts');
        Schema::enableForeignKeyConstraints();
    }
}

class CreateMaterialsTable extends Migration
{
    public function up()
    {
        Schema::create('materials', function (Blueprint $table) {
            $table->engine = 'InnoDB';

            // Keys

            $table->increments('id');

            $table->string('contract_number')->unique();

            $table->unsignedInteger('contract_id');
            $table->foreign('contract_id')->references('id')->on('contracts')
                  ->onDelete('cascade');

            // Others

            ...
        });
    }

    public function down()
    {
        Schema::drop('materials');
    }
}

What am I doing wrong ?

Buglinjo Buglinjo · Accepted Answer · 2017-04-25T15:15:28

You should add ->unsigned() to all your key columns you have. Please use this instead of unsignedInteger() and set the type to integer()

Eloquent - Cannot add foreign key constraint

2 Answers