MySQL Laravel 迁移（错误号：150“外键约束的格式不正确”）

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

提问by FrenchMajesty

I have an orders table and a have a sell_shipping_labelswhich references orders.idas a foreign. However when I run the Laravel migration I get the dreaded error code:

我有一个订单表，并且有一个作为外来sell_shipping_labels引用的表orders.id。但是，当我运行 Laravel 迁移时，我得到了可怕的错误代码：

[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_labelsadd constraint sell_shipping_labels_order_id_foreignforeign 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")

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1005 Can't create table cheapbooks_test。#sql-b5b_b2a(errno: 150 "外键约束的格式不正确") (SQL: alter table sell_shipping_labelsadd constraintsell_shipping_labels_order_id_foreign外键 ( order_id) 引用orders( id))

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[HY000]：一般错误：1005 无法创建表cheapbooks_test。#sql-b5b_b2a(errno: 150 "外键约束的格式不正确")

This is my orderstable schema:

这是我的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();
    });

And this is my sell_shipping_labelsschema:

这是我的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 BEFOREthe table that has the foreign key on it but this isn't a problem for me because my files are in the correct order.

现在我颠倒了互联网，试图找出问题所在。所有关于这个问题的帖子都提到了一个事实，即必须在具有外键的表之前创建订单表，但这对我来说不是问题，因为我的文件顺序正确。

回答by Alexey Mezenin

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

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

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

Laravel 6+ 中的默认迁移使用bigIncrements()，因此您需要使用unsignedBigInteger()方法：

$table->unsignedBigInteger('order_id');

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

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

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

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

$table->unsignedInteger('order_id');

Or:

或者：

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

https://laravel.com/docs/5.5/migrations#foreign-key-constraints

https://laravel.com/docs/5.5/migrations#foreign-key-constraints

回答by Alexey Mezenin

the foreign key must be an "unsignedBigInteger" and it will be fixed, something like this:

外键必须是“unsignedBigInteger”，它会被修复，如下所示：

$table->unsignedBigInteger('user_id');

$table->foreign('user_id')->references('id')->on('users');

回答by Nurul Huda

Primary key and foreign key should be in the same data type.

主键和外键应该是相同的数据类型。

If the primary key is using unsigned big_integer, the foreign key should also be using unsigned big_integer.

如果主键使用 unsigned big_integer，外键也应该使用 unsigned big_integer。

In case laravel 5.8 uses bigIncrementsby default when generating new migration (see this pull request), you should make sure that your foreign keyis also big_incrementor you will get error.

如果 laravel 5.8bigIncrements在生成新迁移时默认使用（请参阅此拉取请求），您应该确保您foreign key的也是如此， big_increment否则会出错。

Table users:

表users：

Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('name');

    ...

}

Table orders:

表orders：

Schema::create('orders', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('user_id');

    ...

    $table->foreign('user_id')->references('id')->on('users');
}

Hope this helps.

希望这可以帮助。

回答by Ayotunde Olubiyo

Laravel 5.8.3 comes with $table->bigIncrements('id');

Laravel 5.8.3 自带 $table->bigIncrements('id');

change it to

将其更改为

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

回答by Anand Mainali

I was also getting the same error. What i was doing in userstable is,

我也遇到了同样的错误。我在用户表中所做的是，

$table->unsignedInteger('role_id')->default(2); table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');

$table->unsignedInteger('role_id')->default(2); table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');

But i have created the role table after creating users table. So, i edited the role migration file name date before the users table filename date. Like this,

但是我在创建用户表后创建了角色表。因此，我在用户表文件名日期之前编辑了角色迁移文件名日期。像这样，

2013_01_22_091213_create_roles_table.php
2014_10_12_000000_create_users_table.php

2013_01_22_091213_create_roles_table.php
2014_10_12_000000_create_users_table.php

And finally it works. Maybe sometime you may get this problem. So, i posted it.

最后它起作用了。也许有时你会遇到这个问题。所以，我发布了它。

回答by Abid Shah

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.

检查您的迁移顺序。如果您的 migrate 命令试图在 orders 表之前创建 sell_shipping_labels 表，这将在 MySQL 中发生。它似乎继续创建迁移日期，从最旧到最新。换句话说，它试图引用的表上的 order_id 应该存在。

I have faced the same problem and I change create migration date.

我遇到了同样的问题，我更改了创建迁移日期。

回答by Dave

To anyone looking at this using laravel 5.8.x I fixed this by changing this

对于使用 laravel 5.8.x 查看此内容的任何人，我通过更改此内容来解决此问题

$table->unsignedInteger('foreign_id');

to this

对此

$table->unsignedBigInteger('foreign_id');

That's due to the use of bigIncrements. You could instead remove chance bigIncrements to increments on both sides of the relation

这是由于使用了 bigIncrements。您可以改为在关系的两侧删除机会 bigIncrements 以增加

回答by Odubola Oluwatimilehin

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.

始终确保要对其施加外部约束的文件的迁移发生在父迁移之后。对于后者，请确保它是 unsignedBigInteger，尽管 laravel 的前版本（<5.4）可以忽略此类型转换错误。

回答by Yogesh More

For laravel 6+ users, I agreed with the top 2 answers its all depends on laravel versions, For the latest versions users idcolumn uses big integer. So referencing the users idfrom current migration you need to use unsignedBigIntegeras a reference key. Bellow is a migration example for laravel 6.5.*, Whenever we assign foreign keyKeep in mind of your current laravel version

对于 Laravel 6+ 用户，我同意前 2 个答案，这完全取决于 Laravel 版本，对于最新版本，用户id列使用big integer. 因此，id从当前迁移中引用用户需要unsignedBigInteger用作引用键。Bellow 是laravel 6.5.*的迁移示例，每当我们分配时foreign key请记住您当前的 laravel 版本

Schema::create('galleries', function (Blueprint $table) {
        $table->bigIncrements('id');
        ==>$table->unsignedBigInteger('user_id');
        $table->string('title');
        $table->string('description');
        $table->timestamps();
        ==>$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });

回答by Hossni Mubarak

I faced the same problem today. My laravel version is 5.8.29. I solved the problem by doing:

我今天遇到了同样的问题。我的 Laravel 版本是 5.8.29。我通过执行以下操作解决了问题：

$table->bigIncrements('id'); //current table primary key and id
$table->unsignedBigInteger('user_id'); // foreigh key
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

Hope this works.

希望这有效。