Laravel迁移“无法添加外键约束" MySQL数据库错误 [英] Laravel migration "Cannot add foreign key constraint" error with MySQL database

问题描述

我正在开发Laravel应用程序.我正在使用MySQL作为数据库.我已经创建了一个模型类，并试图在其上运行迁移.但是，当我运行迁移时，添加外键约束时出现错误.这是我到目前为止所做的.

I am developing a Laravel application. I am using MySQL for the database. I have created a model class and am trying to run the migration on it. But when, I run migration, I am getting error with adding the foreign key constraint. This is what I have done so far.

首先，我迁移了运行此命令的内置Laravel用户模型.

First I have migrated the built in Laravel user model running this command.

php artisan migrate

users表是在数据库中创建的.

The users table was created in the database.

然后我创建了另一个运行此命令的模型.

Then I created another model running this command.

php artisan make:model TodoItem -m

然后我将以下代码添加到迁移文件中.

Then I added the following code to the migration file.

class CreateTodoItemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('todo_items', function (Blueprint $table) {
            $table->text('about');
            $table->integer('user_id');
            $table->increments('id');
            $table->timestamps();

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

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

正如您在上面看到的，我通过在todo_items表中添加外键来建立users表和todo_items表之间的关系.然后，我尝试通过运行此命令来迁移TodoItem模型.

AS you can see above, I am building the relationship between users table and todo_items table by adding a foreign key to the todo_items table. Then, I tried to migrate the TodoItem model by running this command.

php artisan migrate

运行命令时，出现此错误.

When I run the command, I got this error.

  Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `todo_items` add constraint `todo_items_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade)

  at /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668| 

 Exception trace:

  1   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  2   PDOStatement::execute()
      /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  Please use the argument -v to see more details.

我在上一个使用Postgresql的项目中做了同样的事情.我工作很好.对于这个项目，我正在使用MySQL数据库，现在它给了我上面的错误.

I did the same in my previous project which is using Postgresql. I was working fine. For this project, I am using MySQL database and now it is giving me the above error.

推荐答案

这是因为您在迁移文件中添加了$table->integer('user_id');.您必须添加unsignedInteger而不是integer，因为users表的原始id列为unsigned(并且两列必须完全相同).

This is because you added $table->integer('user_id'); to your migration file. You must add an unsignedInteger instead of an integer, because the original id column of the users table is unsigned (and both columns must be exactly the same).

从Laravel 5.8开始，默认users表的id列类型不再是integer.现在是bigInteger.

Since Laravel 5.8, the id column type of the default users table is no longer an integer. It is now a bigInteger.

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