Laravel migration (errno: 150 “Foreign key constra

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.

Answer 1

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();

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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!

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.