mysqllaravellaravel-5laravel-artisanartisan-migrate

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


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

  • 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