Laravel 迁移(errno: 150 “外键约束格式错误")

问题描述

我有一个订单表和一个 sell_shipping_labels ，它引用了 orders.id 作为外部对象.但是，当我运行 Laravel 迁移时，我得到了可怕的错误代码:

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:

[IlluminateDatabaseQueryException]
SQLSTATE[HY000]: General error: 1005 Can't create table cheapbooks_test.#sql-b5b_b2a (errno: 150 "外键约束的格式不正确") (SQL:更改表 sell_shipping_labels 添加约束 sell_shipping_labels_order_id_foreign 外键 (order_id) 引用 orders (id))

[IlluminateDatabaseQueryException]
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))

[DoctrineDBALDriverPDOException]
SQLSTATE[HY000]: 一般错误: 1005 无法创建表 cheapbooks_test.#sql-b5b_b2a (errno: 150 "外键约束形成不正确")

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

这是我的orders表架构:

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

这是我的sell_shipping_labels架构:

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.

推荐答案

由于 increments() 创建了一个无符号整数列，因此您需要将外键列也定义为无符号整数.

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

Laravel 6+ 中的默认迁移使用 bigIncrements()，所以你需要使用 unsignedBigInteger() 方法:

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

$table->unsignedBigInteger('order_id');

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

对于旧版本 Laravel 中的默认迁移，使用 unsignedInteger() 方法:

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

$table->unsignedInteger('order_id');

或者:

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

https://laravel.com/docs/5.5/migrations#foreign-关键约束

这篇关于Laravel 迁移(errno: 150 “外键约束格式错误")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！