无法在Laravel迁移中创建外键

Question

我正在从事一个项目，我正在使用laravel 5.8。这里我想创建一个外键。我试了很多次，但每次都收到一条关于迁移时间的错误消息。有三个表一个是行业，是小学和学生，这些是第二个表。我想在这两个辅助表trade_id上创建一个外键作为键的名称。

这是交易表（主表）。

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

 class CreateTradesTable extends Migration
{
/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('trades', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('trade', 32)->nullable();
        $table->string('unit', 32)->nullable();
        $table->string('class_room', 32)->nullable();
        $table->string('total_class_room', 32)->nullable();
        $table->string('workshop', 32)->nullable();
        $table->string('total_workshops', 32)->nullable();
        $table->string('remarks', 32)->nullable();
        $table->timestamps();
        $table->softDeletes();
    });
}

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

这是学生表（辅助表）。

<?php

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

class CreateStudentsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('students', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name', 32)->nullable();
            $table->string('father_name', 32)->nullable();
            $table->string('uid', 12)->nullable();
            $table->date('dob');
            $table->string('sex', 6)->nullable();
            $table->string('status', 16)->nullable();
            $table->integer('trade_id')->unsigned()->nullable();
            $table->foreign('trade_id')->references('id')->on('trades')->onDelete('cascade');            
            $table->string('shift', 32)->nullable();
            $table->string('session', 32)->nullable();
            $table->timestamps();
            $table->softDeletes();
        });


    }

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

这是放置表（辅助表）。

<?php

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

class CreatePlacementsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('placements', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('roll_no', 32)->nullable();
            $table->string('name', 32)->nullable();
            $table->integer('trade_id')->unsigned()->nullable();  
            $table->foreign('trade_id')->references('id')->on('trades')->onDelete('cascade');                      
            $table->year('year_of_passing');
            $table->string('organization_name', 32)->nullable();
            $table->float('salary_on_joining', 8,2);
            $table->timestamps();
            $table->softDeletes();
        });

    }

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

在迁移时出现错误。

$ php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table
Migrating: 2019_05_22_132427_create_notices_table
Migrated:  2019_05_22_132427_create_notices_table
Migrating: 2019_05_22_132600_create_students_table

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `students` add co
nstraint `students_trade_id_foreign` foreign key (`trade_id`) references `trades` (`id`) on delete cascade)

  at C:\laragon\www\projects\UIIT-admin\uiti_admin\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")
      C:\laragon\www\projects\UIIT-admin\uiti_admin\vendor\laravel\framework\src\Illuminate\Database\Connection.php:458

  2   PDOStatement::execute()
      C:\laragon\www\projects\UIIT-admin\uiti_admin\vendor\laravel\framework\src\Illuminate\Database\Connection.php:458

  Please use the argument -v to see more details.

Answer 1

按照以下步骤更改外键列代码

//In students

Schema::create('students', function (Blueprint $table) {         
      $table->unsignedBigInteger('trade_id')->nullable();
      $table->foreign('trade_id')->references('id')->on('trades')->onDelete('cascade');                   
});



//In placements

Schema::create('placements', function (Blueprint $table) {
      $table->unsignedBigInteger('trade_id')->nullable();;
      $table->foreign('trade_id')->references('id')->on('trades')->onDelete('cascade');    
});

Answer 2

您已将外键定义为整数类型，但交易表中的primery键是bigintger类型

试试这个吧

 $table->bigInteger('trade_id')->unsigned()->nullable();
 $table->foreign('trade_id')->references('id')->on('trades')->onDelete('cascade');