Laravel迁移创建自定义外键时出现SQL语法错误如何解决

错误根因 #

1. 方法拼写错误：外键关联方法正确拼写为references，你代码中误写为refrences，导致Laravel无法正确生成关联字段的SQL语句，最终触发语法报错
2. 可空属性设置位置错误：nullable()是字段属性，不能加在外键定义语句后，需要在字段定义阶段设置
3. 字段定义不符合需求：你需要新增出生、死亡两组共6个关联字段，现有代码只定义了通用的city_id、state_id、country_id三个字段

修正后的迁移代码 #

Schema::create('names', function (Blueprint $table) {
    $table->id();
    $table->string('fname')->nullable();
    $table->string('mname')->nullable();
    $table->string('lname')->nullable();
    $table->string('image')->nullable();
    $table->string('nickname')->nullable();
    $table->string('height')->nullable();
    $table->string('gender',15)->nullable();
    $table->date('dob')->comment('Date of Birth')->nullable();
    $table->date('dod')->comment('Date of Death')->nullable();   
    // 出生相关关联字段
    $table->unsignedBigInteger('birth_city_id')->nullable();
    $table->unsignedBigInteger('birth_state_id')->nullable();
    $table->unsignedBigInteger('birth_country_id')->nullable();
    // 死亡相关关联字段
    $table->unsignedBigInteger('death_city_id')->nullable();
    $table->unsignedBigInteger('death_state_id')->nullable();
    $table->unsignedBigInteger('death_country_id')->nullable();
    // 自定义外键关联
    $table->foreign('birth_city_id', 'birth_city_id_fk')->references('id')->on('cities');
    $table->foreign('birth_state_id', 'birth_state_id_fk')->references('id')->on('states');
    $table->foreign('birth_country_id', 'birth_country_id_fk')->references('id')->on('countries');
    $table->foreign('death_city_id', 'death_city_id_fk')->references('id')->on('cities');
    $table->foreign('death_state_id', 'death_state_id_fk')->references('id')->on('states');
    $table->foreign('death_country_id', 'death_country_id_fk')->references('id')->on('countries');
    $table->text('mini_bio')->nullable();
    $table->boolean('is_active')->nullable();
    $table->softDeletes();
    $table->timestamps();
});

额外说明 #

外键名可以根据你的需求自定义，只要保证同一个库中外键名不重复即可，上述代码中后缀加_fk是为了避免和字段名重名引发冲突。

内容的提问来源于stack exchange，提问作者Hemant Kumar