如何通过laravel迁移在外键方面链接两个表

Question

我试图将thread表链接到message表，但在迁移时，我收到一个错误，上面写着：

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `thread` add constraint `thread_id_foreign` foreign key (`id`) references `message` (`thread_id`))

我做错了什么？我该如何做到这一点？

用户迁移：

public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('email')->unique();
$table->string('full_name');
$table->string('password');
$table->string('bio');
$table->rememberToken();
$table->timestamps();
});
}

以下是线程迁移：

Schema::create('thread', function (Blueprint $table) {
$table->id();
$table->string('title');
$table->foreign('id')
->references('thread_id')
->on('message');
});

以下是消息迁移：

Schema::create('message', function (Blueprint $table) {
$table->id();
$table->unsignedInteger('user_id');
$table->unsignedInteger('thread_id');
$table->string('body');
$table->foreign('user_id')
->references('id')
->on('users')
->onDelete('cascade');
});

Answer 1

您需要对外键列的使用unsignedBigInteger

您应该为子表而不是父表设置外键

试试这个：

Schema::create('thread', function (Blueprint $table) {
$table->id();
$table->string('title');
});

Schema::create('message', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('user_id');
$table->unsignedBigInteger('thread_id');
$table->string('body');

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

Answer 2

如果要将线程表链接到消息，请像这样使用

注意：请确保先创建消息迁移，然后创建线程迁移

Schema::create('message', function (Blueprint $table) {
$table->id();
// $table->unsignedInteger('thread_id');-> No Need for this column in here
$table->string('body');
$table->foreignId('user_id')->constrained('users')->onDelete('cascade');
});

Schema::create('thread', function (Blueprint $table) {
$table->id();
$table->string('title');
$table->foreignId('message_id')->constrained('message')->onDelete('cascade');
});

但是如果你想把消息表链接到线程表，就不要像这个那样使用它

Schema::create('thread', function (Blueprint $table) {
$table->id();
$table->string('title');
});
Schema::create('message', function (Blueprint $table) {
$table->id();
$table->foreignId('thread_id')->constrained('thread')->onDelete('cascade');
$table->string('body');
$table->foreignId('user_id')->constrained('users')->onDelete('cascade');
});

Answer 3

使用此示例来更正代码。

Laravel 6+使用bigIncrements((，因此您需要使用unsignedBigInteger((方法更改(如果您有(：

Schema::create('subcounties', function (Blueprint $table) {
$table->id();
$table->Integer('countyid'); //change this line
$table->string('subcounty');
$table->timestamps();
$table->foreign('countyid')->references('id')
->on('counties')
->onDelete('cascade');
});
}

更改为：

Schema::create('subcounties', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('countyid')->nullable(); //change it to this
$table->string('subcounty');
$table->timestamps();
$table->foreign('countyid')->references('id')->on('counties')->onDelete('cascade');
});
}