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 tablecheapbooks_test
.#sql-b5b_b2a
(errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter tablesell_shipping_labels
add constraintsell_shipping_labels_order_id_foreign
foreign key (order_id
) referencesorders
(id
))[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[HY000]: General error: 1005 Can't create tablecheapbooks_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.
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