PHP Laravel PDOException 在外键约束中引用列和被引用列的一般错误不兼容

PHP Laravel PDOException General error referencing column and referenced column in a foreign key constraint are incompatible

提问by Chakeeel

I am currently doing migrations in Laravel via the Terminal, and have these two errors when attempting to use php artisan migrate; I will print errors below:

我目前正在通过终端在 Laravel 中进行迁移，并且在尝试使用 php artisan migrate 时出现这两个错误；我将在下面打印错误：

Exception trace:

  1   PDOException::("SQLSTATE[HY000]: General error: 3780 Referencing column 'room_id' and referenced column 'id' in foreign key constraint 'contacts_room_id_foreign' are incompatible.")
      /Users/shaquilenoor/Desktop/chatapi/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  2   PDOStatement::execute()
      /Users/shaquilenoor/Desktop/chatapi/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

Based on the code contained within the Exception Trace, the issue seems to be within the code below:

根据异常跟踪中包含的代码，问题似乎出在以下代码中：

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

class CreateContactsTable extends Migration
{
    public function up()
    {
        Schema::create('contacts', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('user1_id');
            $table->unsignedInteger('user2_id');
            $table->integer('room_id')->unique();
            $table->timestamps();
            $table->foreign('room_id')->references('id')->on('rooms');
            $table->foreign('user1_id')->references('id')->on('users');
            $table->foreign('user2_id')->references('id')->on('users');
        });
    }

    public function down()
    {
        Schema::dropIfExists('contacts');
    }
}

any ideas on how I can resolve?

关于我如何解决的任何想法？

采纳答案by Michael Mano

This is due to the foreign keys being set before the rooms table being created. you can fix this by doing the following.

这是因为在创建 rooms 表之前设置了外键。您可以通过执行以下操作来解决此问题。

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

class CreateContactsTable extends Migration
{
    public function up()
    {
        Schema::disableForeignKeyConstraints();
        Schema::create('contacts', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('user1_id');
            $table->unsignedInteger('user2_id');
            $table->integer('room_id')->unique();
            $table->timestamps();
            $table->integer('room_id')->unsigned();
            $table->integer('user1_id')->unsigned();
            $table->integer('user2_id')->unsigned();
            $table->foreign('room_id')->references('id')->on('rooms');
            $table->foreign('user1_id')->references('id')->on('users');
            $table->foreign('user2_id')->references('id')->on('users');
        });
        Schema::enableForeignKeyConstraints();
    }

    public function down()
    {
        Schema::disableForeignKeyConstraints();
        Schema::dropIfExists('contacts');
        Schema::enableForeignKeyConstraints();
    }
}

回答by Payam Khaninejad

If you're on Laravel 5.8new migration changed to big increments, so for fixining refrencing error just change integerto bigInteger, for example:

如果您使用的是Laravel 5.8新迁移更改为大增量，因此为了修复引用错误，只需将integer更改为bigInteger，例如：

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

Will changed to:

将更改为：

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

回答by Emeka Obianom

Either change original migration from

要么更改原始迁移

bigIncrements()

大增量（）

to just

只是

increments();

增量（）；

---

---

OrIn your foreign key column do

或者在你的外键列中做

bigInteger()

大整数()

instead of

代替

integer()

整数（）

回答by tanjiya

In Laravel 6.0, migrations are as same as @Payam Khaninejadmentioned. i.e.

在Laravel 6.0 中，迁移与提到的@Payam Khaninejad相同。IE

$table->bigInteger('user_id')->unsigned()->index();

I am updating that for Laravel 6.0because, I was following an old tutorial that has shown the same error.

我正在为Laravel 6.0更新它，因为我正在关注一个显示相同错误的旧教程。

回答by Jean-Roch B.

Also in laravel 7.x you may have to change:

同样在 laravel 7.x 中，您可能需要更改：

$table->increments('id'); // UNSIGNED INTEGER
// to 
$table->id(); // UNSIGNED BIGINT

回答by Lucas Piazzi

In this case, room_idshould be unsigned. Try this:

在这种情况下，room_id应该是未签名的。尝试这个：

 public function up()
    {
        Schema::create('contacts', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('user1_id');
            $table->unsignedInteger('user2_id');
            $table->integer('room_id')->unique()->unsigned();
            $table->timestamps();
            $table->foreign('room_id')->references('id')->on('rooms');
            $table->foreign('user1_id')->references('id')->on('users');
            $table->foreign('user2_id')->references('id')->on('users');
        });
    }