Laravel: Foreign key incorrectly formed

1
votes

So I am busy with making this migration and afther a ton of research, he still gives me the following error message:

SQLSTATE[HY000]: General error: 1005 Can't create table dev.bindings (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table bindings add constraint bindings_users_id_foreign foreign key (users_id) references users (id) on delete cascade on update cascade)

Code:

Schema::create('bindings', function (Blueprint $table) {
    $table->unsignedInteger('users_id');

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

Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id');
});

Versions:

PHP: v7.3
Laravel: v5.8.4
MariaDB: v10.3.13
Homestead: v8.1.0

As that I am aware, this should form a foreign key correctly. Any help would be appriciated!

2
phplaraveldatabase-migration
What is the data type of your id column in the users table? - Script47
@Script47 It's a bigIncrement type, also tried Increment but that didn't help :( || Edit: added it to the main post as well - StPronk

2 Answers

2
votes

Update: as other answers stated, i did not notice it, you will need to create the users table, before the foreign key can be created.

When you do foreign keys, both fields will need to have the same properties. So in your case if you use bigIncrements your users_id needs to be an unsigned big int.

// User table needs to be created first
Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id');
});

Schema::create('bindings', function (Blueprint $table) {
    $table->bigInteger('users_id')->unsigned();

    ...
});

Also the standard is to name it user_id, this will make it easier to do relations in Laravel.

1
votes

In your migration, place your parent table migration on top then child table

    Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
        });


Schema::create('bindings', function (Blueprint $table) {
        $table->unsignedBigInteger('user_id');

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

And then run php artisan migrate command.