laravel 迁移：无法添加外键约束

Migration: Cannot add foreign key constraint

提问by 001221

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

我正在尝试在 Laravel 中创建外键，但是当我使用迁移表artisan时，抛出以下错误：

[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`))     

My migration code is as so:

我的迁移代码如下：

priorities migration file

优先级迁移文件

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

users migration file

用户迁移文件

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

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_projectand project_tasksetc.

关于我做错了什么的任何想法，我想现在就得到这个，因为我有很多表需要创建，例如用户、客户、项目、任务、状态、优先级、类型、团队。理想我想创建与外键，i..e持此数据表clients_project和project_tasks等。

Hope someone can help me to get started.

希望有人可以帮助我开始。

回答by Antonio Carlos Ribeiro

Add it in two steps, and it's good to make it unsigned too:

分两步添加，也可以不签名：

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

   Schema::table('priorities', function($table) {
       $table->foreign('user_id')->references('id')->on('users');
   });

}

回答by haakym

Question already answered, but hope this might help someone else.

问题已经回答，但希望这可能对其他人有所帮助。

This error occurred for me because I created the migration table with the foreign key in it firstly before the key existed as a primary key in it's original table. Migrations get executed in the order they were created as indicated by the file name generated after running migrate:make. E.g. 2014_05_10_165709_create_student_table.php.

这个错误发生在我身上，因为我首先创建了带有外键的迁移表，然后该键作为其原始表中的主键存在。迁移按照运行后生成的文件名所指示的创建顺序执行migrate:make。例如2014_05_10_165709_create_student_table.php。

The solution was to rename the file with the foreign key to an earlier time than the file with the primary key as recommended here: http://forumsarchive.laravel.io/viewtopic.php?id=10246

解决方案是将带有外键的文件重命名为比此处推荐的带有主键的文件更早的时间：http: //forumsarchive.laravel.io/viewtopic.php?id=10246

I think I also had to add in $table->engine = 'InnoDB';

我想我也必须添加 $table->engine = 'InnoDB';

回答by chebaby

Laravel ^5.8

Laravel ^5.8

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.

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 referencea column created using the bigIncrements method.

Source: Migrations & bigIncrements

从 Laravel 5.8 开始，迁移存根默认在 ID 列上使用 bigIncrements 方法。以前，ID 列是使用增量方法创建的。

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

资料来源：Migrations & bigIncrements

---

---

Example

例子

Let's imagine you are building a simple role-based application, and you need to references user_idin the PIVOTtable "role_user".

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

2019_05_05_112458_create_users_table.php

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

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

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 bigIntegerin role_usertable or change bigIncrementsmethod to incrementsmethod in userstable and use the commented line in the pivot table, it's up to you.

如您所见，注释行将抛出查询异常，因为如升级说明中所述，外键列必须为相同类型，因此您需要将前键（在本例中为user_id）更改为BIGINTEGER在ROLE_USER表或改变bigIncrements方法增量的方法，用户表，并使用数据透视表中的注释行，就看你了。

---

---

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

我希望我能够向你澄清这个问题。

回答by AdrianCR

In my case, the issue was that the main table already had records in it and I was forcing the new column to not be NULL. So adding a ->nullable() to the new column did the trick. In the question's example would be something like this:

就我而言，问题是主表中已经有记录，我强制新列不为 NULL。因此，向新列添加 ->nullable() 就成功了。在问题的例子中会是这样的：

$table->integer('user_id')->unsigned()->nullable();

$table->integer('user_id')->unsigned()->nullable();

or:

或者：

$table->unsignedInteger('user_id')->nullable();

$table->unsignedInteger('user_id')->nullable();

Hope this helps somebody!

希望这对某人有帮助！

回答by Daniele

In my case the problem was that the auto-generated migration for the userstable was setting

在我的情况下，问题是表的自动生成的迁移users正在设置

...
$table->bigIncrements('id');
...

So I had to change the column type

所以我不得不改变列类型

$table->bigInteger('id');

to make my migration with the foreign key work.

使我的外键迁移工作。

This with laravel 5.8.2

这与laravel 5.8.2

回答by Vicky

In my case the problem was with migration timing be careful while creating migrations firstly create the child migration than the base migration. Because if you create base migration first which have your foreign key will look for child table and there wont be table which then throw an exception.

在我的情况下，问题出在迁移时间上，在创建迁移时要小心，首先创建子迁移而不是基本迁移。因为如果您首先创建具有外键的基本迁移，则会查找子表，并且不会有随后抛出异常的表。

Further more:

此外：

When you create migration it has a timestamp in the beginning of it. lets say you have created a migration catso it will look like 2015_08_19_075954_the_cats_time.phpand it has this code

创建迁移时，它的开头有一个时间戳。假设您已经创建了一个迁移猫，因此它看起来像2015_08_19_075954_the_cats_time.php并且它有这个代码

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class TheCatsTime extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('cat', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');  
            $table->date('date_of_birth');
            $table->integer('breed_id')->unsigned()->nullable(); 
        });

        Schema::table('cat', function($table) {
        $table->foreign('breed_id')->references('id')->on('breed');
      });
    }

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

And after creating the base table you create another migration breedwhich is child table it has its own creation time and date stamp. The code will look like :

创建基表后，您创建另一个迁移品种，即子表，它有自己的创建时间和日期戳。代码将如下所示：

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class BreedTime extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('breed', function (Blueprint $table) {
             $table->increments('id');    
             $table->string('name');
        });
    }

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

it seems these both table are correct but when you run php artisan migrate. It will throw an exception because migration will first create the base table in your database because you have created this migration first and our base table has foreign key constraint in it which will look for child table and the child table doesn't exist which is probably an exception..

看起来这两个表都是正确的，但是当您运行php artisan migrate 时。它会抛出异常，因为迁移将首先在您的数据库中创建基表，因为您首先创建了此迁移，并且我们的基表中有外键约束，它将查找子表，而子表可能不存在一个例外..

So:

所以：

Create child table migration first.

Create base table migration after child migration is created.

php artisan migrate.

首先创建子表迁移。

创建子迁移后创建基表迁移。

php工匠迁移。

done it will work

完成它会起作用

回答by ldt

In my case I just change the order migrations are executed manually so table users is created first.

就我而言，我只是更改手动执行迁移的顺序，因此首先创建表用户。

In folder database/migrations/ your migration filename have this format: year_month_day_hhmmss_create_XXXX_table.php

在文件夹 database/migrations/ 中，您的迁移文件名具有以下格式：year_month_day_hhmmss_create_XXXX_table.php

Just rename create user file so creation date of your table priorities table is set later than user date (even one second later is enough)

只需重命名创建用户文件，以便您的表优先级表的创建日期设置为晚于用户日期（即使晚一秒就足够了）

回答by Dhara Talaviya

In laravel 5.8, the users_table uses bigIncrements('id')data type for the primary key. So that when you want to refer a foreign key constraint your user_idcolumn needs to be unsignedBigInteger('user_id')type.

在 laravel 5.8 中， users_table 使用bigIncrements('id')数据类型作为主键。因此，当您要引用外键约束时，您的user_id列需要unsignedBigInteger('user_id')输入。

回答by Capfer

I was having the same issue using Laravel 5.8.After taking a closer look to laravel docs, moreover here Migrations & bigIncrements. The way I solved it is by adding primary keys "$table->bigIncrements('id')"to every single table that is related to the table "users"and its associations, in my case the table "role". Lastly, I had "$table->unsignedBigInteger"for associating roles to users (Many-to-Many), that is, table "role_user".

我在使用Laravel 5.8时遇到了同样的问题。在仔细查看 laravel 文档之后，此外还有迁移和 bigIncrements。我解决它的方法是将主键"$table->bigIncrements('id')" 添加到与表"users"及其关联相关的每个表中，在我的例子中是表"role"。最后，我有"$table->unsignedBigInteger"用于将角色关联到用户（多对多），即表"role_user"。

1. Users table

    Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

2. Roles Table
    Schema::create('roles', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name')->unique();
        $table->string('display_name')->nullable();
        $table->string('description')->nullable();
        $table->timestamps();
    });

3. Table role_user
Schema::create('role_user', function (Blueprint $table) {
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('role_id');
            $table->foreign('user_id')->references('id')->on('users')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->foreign('role_id')->references('id')->on('roles')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->primary(['user_id', 'role_id']);
        });

回答by Slycreator

I had this issue with laravel 5.8 and adding this code to where ever i am adding user_id did the trick.

我在 laravel 5.8 上遇到了这个问题，并将这段代码添加到我添加 user_id 的地方就行了。

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

then i ran $ php artisan migrate:refresh

然后我跑了 $ php artisan migrate:refresh