迁移:无法添加外键约束 [英] Migration: Cannot add foreign key constraint

问题描述

我正在尝试在Laravel中创建外键，但是当我使用artisan迁移表时，会引发以下错误:

I'm trying to create foreign keys in Laravel however when I migrate my table using artisan i am thrown the following error:

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL
: alter table `priorities` add constraint priorities_user_id_foreign foreign 
key (`user_id`) references `users` (`id`))     

我的迁移代码如下:

优先级迁移文件

public function up()
{
    //
    Schema::create('priorities', function($table) {
        $table->increments('id', true);
        $table->integer('user_id');
        $table->foreign('user_id')->references('id')->on('users');
        $table->string('priority_name');
        $table->smallInteger('rank');
        $table->text('class');
        $table->timestamps('timecreated');
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    //
    Schema::drop('priorities');
}

用户迁移文件

public function up()
{
    //
    Schema::table('users', function($table)
    {
    $table->create();
    $table->increments('id');
    $table->string('email');
    $table->string('first_name');
    $table->string('password');
    $table->string('email_code');
    $table->string('time_created');
    $table->string('ip');
    $table->string('confirmed');
    $table->string('user_role');
    $table->string('salt');
    $table->string('last_login');

    $table->timestamps();
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    //
        Schemea::drop('users');
}

关于我做错了什么的任何想法，我想现在就解决，因为我有很多需要创建的表，例如用户，客户，项目，任务，状态，优先级，类型，团队.理想情况下，我想创建使用外键(即clients_project和project_tasks等)保存此数据的表.

Any ideas as to what I've done wrong, I want to get this right now, as I've got a lot of tables I need to create e.g. Users, Clients, Projects, Tasks, Statuses, Priorities, Types, Teams. Ideally I want to create tables which hold this data with the foreign keys, i..e clients_project and project_tasks etc.

希望有人可以帮助我入门.

Hope someone can help me to get started.

推荐答案

Laravel ^ 5.8

从Laravel 5.8起，迁移存根在ID上使用bigIncrements方法 默认情况下为列.以前，ID列是使用 增量法.

As of Laravel 5.8, migration stubs use the bigIncrements method on ID columns by default. Previously, ID columns were created using the increments method.

这不会影响您项目中的任何现有代码；但是 请注意，外键列必须是同一类型.因此， 使用增量方法创建的列无法引用 使用bigIncrements方法创建的.

This will not affect any existing code in your project; however, be aware that foreign key columns must be of the same type. Therefore, a column created using the increments method can not reference a column created using the bigIncrements method.

来源:迁移和bigIncrements

---

示例

假设您正在构建一个基于角色的简单应用程序，并且需要在 PIVOT 表"role_user" 中引用 user_id .

---

Example

Let's imagine you are building a simple role-based application, and you need to references user_id in the PIVOT table "role_user".

2019_05_05_112458_create_users_table.php

// ...

public function up()
{
    Schema::create('users', function (Blueprint $table) {

        $table->bigIncrements('id');

        $table->string('full_name');
        $table->string('email');
        $table->timestamps();
    });
}

2019_05_05_120634_create_role_user_pivot_table.php

// ...

public function up()
{
    Schema::create('role_user', function (Blueprint $table) {

        // this line throw QueryException "SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint..."
        // $table->integer('user_id')->unsigned()->index();

        $table->bigInteger('user_id')->unsigned()->index(); // this is working
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });
}

如您所见，注释行将引发查询异常，因为如升级说明中所述，外键列必须为同一类型，因此您需要更改在 role_user 表中指向 bigInteger 的键(在本例中为 user_id )或将 bigIncrements 方法更改为 users 表中的增量方法并使用数据透视表中的注释行，这取决于您.

As you can see, the commented line will throw a query exception, because, as mentioned in the upgrade notes, foreign key columns must be of the same type, therefore you need to either change the foreing key (in this example it's user_id) to bigInteger in role_user table or change bigIncrements method to increments method in users table and use the commented line in the pivot table, it's up to you.

我希望我能够向您澄清这个问题.

I hope i was able to clarify this issue to you.

这篇关于迁移:无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！