Laravel migration (errno: 150 "Foreign key constraint is incorrectly formed")

Question

I have an orders table and a have a sell_shipping_labels which references orders.id as a foreign. However when I run the Laravel migration I get the dreaded error code:

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1005 Can't create table cheapbooks_test.#sql-b5b_b2a (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table sell_shipping_labels add constraint sell_shipping_labels_order_id_foreign foreign key (order_id) references orders (id))

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[HY000]: General error: 1005 Can't create table cheapbooks_test.#sql-b5b_b2a (errno: 150 "Foreign key constraint is incorrectly formed")

This is my orders table schema:

   Schema::create('orders', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id');
        $table->integer('book_id');
        $table->integer('status_id');
        $table->double('payment_amount')->nullable();
        $table->timestamp('received_at')->nullable();
        $table->timestamp('paid_at')->nullable();
        $table->timestamps();
        $table->softDeletes();
    });

And this is my sell_shipping_labels schema:

Schema::create('sell_shipping_labels', function (Blueprint $table) {
        $table->increments('id');
        $table->unsignedInteger('order_id');
        $table->string('shippo_object_id');
        $table->string('label_url');
        $table->string('tracking_url');
        $table->string('tracking_number');
        $table->timestamp('arrived_at');
        $table->timestamps();
        $table->softDeletes();

        $table->foreign('order_id')->references('id')->on('orders');
    });
}

Now I've flipped the internet upside down trying to figure out the problem. All of the post about this problem all refer to the fact that the orders table must be created BEFORE the table that has the foreign key on it but this isn't a problem for me because my files are in the correct order.

Solution 1

Since increments() creates an unsigned integer column, you need to define the foreign key column as unsigned integer too.

Default migrations in Laravel 6+ use bigIncrements(), so you need to use unsignedBigInteger() method:

$table->unsignedBigInteger('order_id');

https://laravel.com/docs/6.x/migrations#foreign-key-constraints

For default migrations in older versions of Laravel use unsignedInteger() method:

$table->unsignedInteger('order_id');

Or:

$table->integer('order_id')->unsigned();

https://laravel.com/docs/5.5/migrations#foreign-key-constraints

Solution 2

the foreign key must be an "unsignedBigInteger" and it will be fixed, something like this:

$table->unsignedBigInteger('user_id');

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

Solution 3

Primary key and foreign key should be in the same data type.

If the primary key is using unsigned big_integer, the foreign key should also be using unsigned big_integer.

In case laravel 5.8 uses bigIncrements by default when generating new migration (see this pull request), you should make sure that your foreign key is also unsigned big_integer or you will get error.

Table users:

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

    ...

}

Table orders:

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

    ...

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

Hope this helps.

Solution 4

I was also getting the same error. What i was doing in users table is,

$table->unsignedInteger('role_id')->default(2); table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');

But i have created the role table after creating users table. So, i edited the role migration file name date before the users table filename date. Like this,

2013_01_22_091213_create_roles_table.php
2014_10_12_000000_create_users_table.php

And finally it works. Maybe sometime you may get this problem. So, i posted it.

Solution 5

Laravel 5.8.3 comes with $table->bigIncrements('id');

change it to

$table->increments('id');
$table->integer('order_id')->unsigned();

View more solutions

Comments

[afaolek]

Laravel 7+ allows you to do $table->foreignId('order_id')->constrained(); instead of $table->foreign('order_id')->references('id')->on('orders');

[Oluwaseyitan Baderinwa]

@afaolek suggestion worked for me. Haven't tried other answers though (don't see the need to anyway)

[FrenchMajesty]

I've updated my table schema but I still get the same error code.

[Alexey Mezenin]

@FrenchMajesty did you tried to recreate DB? If you did, please post the new error message, because I'm pretty sure the syntax is correct.

[FrenchMajesty]

Deleting and re-creating my database as well as your answer did the trick. Thank you!

[FrenchMajesty]

If you read the post carefully I wrote that many people mentioned the order of migration and that was something I had checked was good so that wasn't the issue.

[kushal parikh]

This works for me. Thanks. Also, we can make order_id as bigInteger.

[Jithin]

why is this an issue? and how does we solve it when it is bigincrements?

[ageans]

Thanks, a lot. This method is working for me. But I don't understand why the other methods aren't working for me. Any insight would be nice.

[ageans]

Thanks, a lot. This method is working for me. But I don't understand why the other methods aren't working for me. Any insight would be nice.

[GTHell]

I think because by default the Laravel uses BigIncrement on the id field and just like typecasting, int64 -> int32 (Will throw exception), the type of the reference_id should be Big integer also.

[Osahady]

perfect and nice!

[thmspl]

Please explain more what and how you did it to solve the problem in your case.

[Liel Dahan]

The best answer here. Thanks!

[Mahdi Safari]

my database type by default is innoDB but doesn't work? how did you do that explain more?

[Mahdi Safari]

this works for me thanks. I try any of above answers doesn't work but your answer magically works thank.

[Jk33]

I had to use unsignedBigInteger (as on the answer below) on latest version.

[Akash Sethi]

$table->unsignedBigInteger('order_id'); this worked for me. Thank you

[Leon]

Thanks, that was the problem !