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

2021-12-18 00:00:00 mysql laravel laravel-5 laravel-artisan artisan-migrate

我有一个订单表和一个 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-关键约束

相关文章