无法使用 Laravel 5 迁移添加外键约束

Cannot add foreign key constraint using Laravel 5 migration

提问by Chris Schmitz

I'm trying to create a migration for an orderstable. This table has a foreign key constraint for two tables: employeesand clients.

我正在尝试为orders表创建迁移。该表有两个表的外键约束：employees和clients。

The schema for the orders table:

订单表的架构：

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

    $table->integer('client_id')->unsigned();
    $table->foreign('client_id')->references('id')->on('clients');

    $table->integer('employee_id')->unsigned();
    $table->foreign('employee_id')->references('id')->on('employees');

    $table->text('description');
    $table->string('status');
    $table->date('submitted_on');
    $table->date('completed_on');
    $table->timestamps();
});

The schema for the employees table:

员工表的架构：

Schema::create('employees', function (Blueprint $table) {
    $table->increments('id');
    $table->string('type');
    $table->timestamps();
});

The schema for the clients table:

客户表的架构：

Schema::create('clients', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->integer('user_id')->unsigned();
    $table->foreign('user_id')
            ->references('id')
            ->on('users')
            ->onDelete('cascade');
    $table->timestamps();
});

When I run the migration, the constraint is created successfully for the clients table, but for some reason it gives an error when attempting to create the constraint for employees:

当我运行迁移时，为客户端表成功创建了约束，但由于某种原因，它在尝试为员工创建约束时出错：

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table ordersadd constraint orders_employee_id_foreign foreign key (employee_id) references employees(id))

[Illuminate\Database\QueryException] SQLSTATE[HY000]：一般错误：1215 无法添加外键约束（SQL：alter table ordersadd constraint orders_employee_id_foreign外键（employee_id）引用employees（id））

I pulled the query it was trying to use out and tried it directly on the database and got the same error:

我拉出它试图使用的查询并直接在数据库上尝试并得到相同的错误：

-- Query:
alter table `orders` add constraint orders_employee_id_foreign foreign key (`employee_id`) references `employees` (`id`)

I'm a bit confused as far as what went wrong. The constraint setup is the same as the clientsconstraint and that is created successfully:

至于出了什么问题，我有点困惑。约束设置与clients约束相同，并且创建成功：

The syntax I'm using to create each of the constraints match. Is there a reason why it would create one but not the other?

我用来创建每个约束匹配的语法。有没有理由为什么它会创建一个而不是另一个？

An aside

一边

@PabloDigiani's solution of checking the creation order of the migrations was correct; the orderstable was being created before the employeestable which caused the error.

@PabloDigiani 检查迁移创建顺序的解决方案是正确的；该orders表是在employees导致错误的表之前创建的。

The solution was to make sure the employeesmigration ran beforethe `orders.

解决方案是确保employees迁移在“订单”之前运行。

The aside I want to add is howto reorder the migrations in laravel. It's stated here in the docs:

我想补充的一点是如何在 laravel 中重新排序迁移。它在文档中说明：

Each migration file name contains a timestamp which allows Laravel to determine the order of the migrations.

每个迁移文件名都包含一个时间戳，允许 Laravel 确定迁移的顺序。

When I checked the timestamp order, employeescame after orders(just the arbitrary order in which I created the migrations via artisan):

当我检查时间戳顺序时，employees之后orders（只是我通过工匠创建迁移的任意顺序）：

The fix was very simple: rename the migration for employeesso that it's timestamp is before orders:

修复非常简单：重命名迁移employees，使其时间戳在之前orders：

Simple fix. Thanks again!

简单的修复。再次感谢！

回答by Pablo Digiani

When migrating a database with Laravel, it is important to check the order in which the tables are created. In this case, employees table should be created before orders table. Otherwise, a foreign key constraint error will be thrown.

使用 Laravel 迁移数据库时，检查表的创建顺序很重要。在这种情况下，应在订单表之前创建员工表。否则会抛出外键约束错误。

Just change the order of the table creation and your migration will run without errors.

只需更改表创建的顺序，您的迁移就会运行而不会出错。