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.

Urology

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

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

Answer 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

Enervated

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

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

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

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

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

Answer 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');

Precentor

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. – Eidolon

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. – Wolff

Answer 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.

Kenweigh

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. – Eidolon

The best answer here. Thanks! – Lowrie

Answer 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.

Negus

Answer 5

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

change it to

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

Cornered

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

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

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

Answer 6

Most times the reason for this error is usually due to the order of which the migration files are listed or error due to type casting.

Always make sure that the migration of the file which the foreign constraints is to be imposed on comes after the parent migration. And for the latter, make sure its an unsignedBigInteger , although former version of laravel (<5.4) could ignore this type casting error.

Taskmaster

Answer 7

1. Migration files should be created in such a way that the parent migration should come first and the migration file with the foreign key next.
2. The foreign key and the primary id in the other table should have exactly similar property. If the primary id is increments then make the foreign key integer('xxx_id')->unsigned();

Unwritten

Thanks, that was the problem ! – Wrier

Answer 8

For those which marked answer didn't work:

Check your table's engine. In my case, I was referencing a MyISAM table in an InnoDB source table. After changing the reference table engine to InnoDB, it worked!

Handiness

Answer 9

Check the order of your migrations. If your migrate command is trying to make the sell_shipping_labels table before the orders table this will occur with MySQL. It seems to go on create migration date, oldest to newest. In other words, the order_id on the table it is trying to reference should exist.

I have faced the same problem and I change create migration date.

Sandstrom

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. – Urology

Answer 10

To anyone looking at this using laravel 5.8.x I fixed this by changing this

$table->unsignedInteger('foreign_id');

to this

$table->unsignedBigInteger('foreign_id');

That's due to the use of bigIncrements. You could instead remove chance bigIncrements to increments on both sides of the relation

Colunga

perfect and nice! – Sieve

Answer 11

Avoid all those two lines of codes with simple which means sell_shipping_labels.order_id references id on orders table as below

Schema::table('sell_shipping_labels', function (Blueprint $table) {
    $table->foreignId('order_id')->constrained('orders');
});

Hobbie

Answer 12

I had the same problem and fixed the issue setting the database type to innoDB

The tables created before the migration where 'MyISAM from an legacy system and the migrated are innoDB by default, so the mix of table types were an issue in my case.

Doggy

Answer 13

I faced the same problem today. My laravel version is 5.8.29. I solved the problem by doing:

$table->bigIncrements('id'); //current table primary key and id
$table->unsignedBigInteger('user_id'); // foreigh key
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

Hope this works.

Educe

Answer 14

For laravel 6+ users, I agreed with the top 2 answers its all depends on laravel versions, For the latest versions users id column uses big integer. So referencing the users id from current migration you need to use unsignedBigInteger as a reference key. Bellow is a migration example for laravel 6.5.*, Whenever we assign foreign key Keep in mind of your current laravel version

Schema::create('galleries', function (Blueprint $table) {
        $table->bigIncrements('id');
        ==>$table->unsignedBigInteger('user_id');
        $table->string('title');
        $table->string('description');
        $table->timestamps();
        ==>$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });

Vivien

Answer 15

my problem was solved when I used bigInteger('user_id')->unsigned();

Ockham

Answer 16

I faced this problem today. I checked all of suggested solutions such as referenced key and foreign key same datatype, same collation in database engine and laravel config (database.php), date order of migrations and other possibility mistakes, but anyone were my solution! last thing I found was onUpdate and onDelete constraints that put in migrations. By removing them my problem solved!

Polycarp

Answer 17

If the problem is still not solved, try it. you need to create the last associated table.

You should first create orders and after create sell_shipping_labels table

To solve the issue you should rename migration files of Category and Users to date of before Meals Migration file that create those before Meals table.

Erectile

Answer 18

[![enter image description here][1]][1]
public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();


        });
    }

I changed $table->bigIncrements('id') to $table->Increments('id')
For this user_id of files table become same integer type as user table field id. After this command worked.

   public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }



For the second table
 {
        Schema::create('files', function (Blueprint $table) {
            $table->increments('id');

});

            Schema::table('files', function($table) {
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
           });
    }

Hillie

Answer 19

I faced this problem today. My parent table primary key datatype and child table data type was same but error was still there. I have found that my parent and child tables storage engine was different. I have fixed this issue by making both tables storage engine InnoDB from my phpmyadmin.

Retinitis

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

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

Answer 20

Better way to add foreign key in Laravel is using the alias. So instead of:

$table->unsignedBigInteger('user_id'); // foreigh key
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

We can simply do this:

$table->foreignId('user_id')->constrained();

And that works for me. Thanks!

Resolution

Answer 21

If your migration is depending on a parent migration, then you need to make sure your parent migration runs first. So what I did was:

1. Copy the content of the current child migration
2. Recreate the migration file using php artisan make:migration create_<models>_table
3. Run php artisan migrate:fresh

Hope that works.

Aufmann

Answer 22

Just to add (for someone who got the same error as mine) that I got an error when I added unique and foreign key in the migration. If I commented on anyone then there would be no error but when I use both; I would get an error. I was getting crazy with what was happening behind the scene in Laravel. Even thought of using DB::statement.

Schema::create('tender_docs', function (Blueprint $table) {
    $table->id()->autoIncrement();
    $table->unsignedBigInteger('tenders_id');
    $table->unique(['tenders_id', 'file_name', 'file_extension'], 'tender_docs_unique')
    $table->foreign('tenders_id')->references('id')->on('tenders')->onUpdate('cascade')->onDelete('cascade');
});

The solution was simple: combine the unique key and foreign key statements into one.

    $table->unique(['tenders_id', 'file_name', 'file_extension'], 'tender_docs_unique')
    ->foreign('tenders_id')->references('id')->on('tenders')->onUpdate('cascade')->onDelete('cascade');

Bounds

Answer 23

Now the shortest and easiest way is to use just the id() in parent table.

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    other columns...

});

and in the child table create foreignId like this.

Schema::create('sell_shipping_labels', function (Blueprint $table) {
    other columns ...

    $table->foreignId('order_id')->constrained();
});

it will take care of everything.

you can also chain ->cascadeOnDelete() method like this.

$table->foreignId('order_id')->constrained()->cascadeOnDelete();

and also remember that parent table's migration should be created first and child table's migration after that because the migrations runs in the sequence in which they were created.

Agranulocytosis