无法在Laravel迁移中创建外键

Question

我正在尝试在user_role表中创建一个外键关系。

user table migration

user_role列是一个外键列

public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->string('user_role')->nullable();
$table->foreign('user_role')->references('role')->on("user_role")->onDelete('cascade');
$table->rememberToken();
$table->foreignId('current_team_id')->nullable();
$table->text('profile_photo_path')->nullable();
$table->timestamps();
});
}

<<p>user_role表/strong>

public function up()
{
Schema::create("user_role",function(Blueprint $table){
$table->increments('id');
$table->string("role")->nullable();
});
}

迁移不适用以下错误生成

IlluminateDatabaseQueryException 
SQLSTATE[HY000]: General error: 1005 Can't create table `ecommerce`.`users` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `users` add constraint `users_user_role_foreign` foreign key (`user_role`) references `user_role` (`role`) on delete cascade)
at F:ecommerce-indianecommercevendorlaravelframeworksrcIlluminateDatabaseConnection.php:678
674▕         // If an exception occurs when attempting to run a query, we'll format the error
675▕         // message to include the bindings with SQL, which will make this exception a
676▕         // lot more helpful to the developer instead of just the database's errors.
677▕         catch (Exception $e) {
➜ 678▕             throw new QueryException(
679▕                 $query, $this->prepareBindings($bindings), $e
680▕             );
681▕         }
682▕
1   F:ecommerce-indianecommercevendorlaravelframeworksrcIlluminateDatabaseConnection.php:471
PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `ecommerce`.`users` (errno: 150 "Foreign key constraint is incorrectly formed")")      
2   F:ecommerce-indianecommercevendorlaravelframeworksrcIlluminateDatabaseConnection.php:471
PDOStatement::execute() 

我做错的外键是什么问题

Answer 1

public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->unsignedBigInteger('user_role')->nullable();
$table->foreign('user_role')->references('id')->on("user_role")->onDelete('cascade');
$table->rememberToken();
$table->foreignId('current_team_id')->nullable();
$table->text('profile_photo_path')->nullable();
$table->timestamps();
});
}

在创建用户迁移之前，创建user_role迁移并在user_role上使用字段id

Answer 2

您的角色必须是唯一的

$table->string("role")->unique();

这是用户迁移的代码

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

then create "user_role;使用此代码迁移。

public function up()
{
Schema::create("user_role",function(Blueprint $table){
$table->increments('id');
$table->string("role")->unique();
});
}

然后创建另一个迁移，如

php artisan make:migration add_user_role_in_users_table

在中添加以下代码

public function up()
{
Schema::table('users', function (Blueprint $table) {
$table->string('user_role')->nullable();
$table->foreign('user_role')->references('role')->on("user_role")->onDelete('cascade');
});
}

，然后运行

php artisan migrate