Laravel Eloquent三级关联复合主键外键约束无效问题求助

问题背景 #

需构建Project→Stage→Lands三级关联系统，要求每个项目下的stage_id和land_id独立从1开始编号（而非全局自增），因此采用复合主键设计，但创建lands表时触发外键约束错误：

SQLSTATE[42830]: Invalid foreign key: 7 ERROR: there is no unique constraint matching given keys for referenced table "stages" (SQL: alter table "lands" add constraint "lands_stage_id_foreign" foreign key ("stage_id") references "stages" ("stage_id") on delete cascade)

错误原因 #

stages表的主键是(project_id, stage_id)复合键，单独的stage_id并非唯一约束（不同项目可存在相同stage_id），而外键必须指向被关联表的唯一键（主键或唯一索引），因此仅用stage_id关联stages表不符合数据库约束要求。

解决方案 #

1. 修正数据库迁移代码 #

核心是让lands表的外键关联stages表的复合主键(project_id, stage_id)，而非单独的stage_id。

Projects迁移（无需修改）

Schema::create('projects', function (Blueprint $table) {
    $table->id("project_id");
    $table->string('name');
    $table->string('region');
    $table->double('area');
    $table->date('start_date')->nullable();
    $table->date('end_date')->nullable();
});

Stages迁移（无需修改）

Schema::create('stages', function (Blueprint $table) {
    $table->unsignedBigInteger('stage_id');
    $table->unsignedBigInteger('project_id');
    $table->foreign('project_id')->references('project_id')->on('projects')->onDelete('cascade');
    $table->primary(['project_id', 'stage_id'], 'project_stage_id');
    $table->string('name');
    $table->double('area');
    $table->date('start_date')->nullable();
    $table->date('end_date')->nullable();
});

Lands迁移（关键修正）

Schema::create('lands', function (Blueprint $table) {
    // 移除全局自增id，使用复合主键
    // $table->id();
    $table->unsignedBigInteger('land_id');
    $table->unsignedBigInteger('stage_id');
    $table->unsignedBigInteger('project_id');
    
    // 关联stages表的复合主键
    $table->foreign(['project_id', 'stage_id'])
          ->references(['project_id', 'stage_id'])
          ->on('stages')
          ->onDelete('cascade');
          
    // 可选：直接关联projects表（已通过stages间接关联，按需保留）
    $table->foreign('project_id')
          ->references('project_id')
          ->on('projects')
          ->onDelete('cascade');
          
    $table->primary(['project_id', 'stage_id', 'land_id'], 'land_primary_key');
    $table->date('cultivation_date');
});

2. Eloquent模型关联配置 #

由于使用复合主键，需在模型中明确指定关联的字段组合：

Project模型

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Project extends Model
{
    protected $primaryKey = 'project_id';
    public $incrementing = true;

    public function stages(): HasMany
    {
        return $this->hasMany(Stage::class, 'project_id', 'project_id');
    }
}

Stage模型

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Stage extends Model
{
    protected $primaryKey = ['project_id', 'stage_id'];
    public $incrementing = false;

    public function project(): BelongsTo
    {
        return $this->belongsTo(Project::class, 'project_id', 'project_id');
    }

    public function lands(): HasMany
    {
        return $this->hasMany(Land::class, ['project_id', 'stage_id'], ['project_id', 'stage_id']);
    }
}

Land模型

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Land extends Model
{
    protected $primaryKey = ['project_id', 'stage_id', 'land_id'];
    public $incrementing = false;

    public function stage(): BelongsTo
    {
        return $this->belongsTo(Stage::class, ['project_id', 'stage_id'], ['project_id', 'stage_id']);
    }

    public function project(): BelongsTo
    {
        return $this->belongsTo(Project::class, 'project_id', 'project_id');
    }
}

3. 实现独立编号逻辑 #

因为stage_id和land_id非自增，创建时需手动生成对应父级下的递增编号：

创建Stage时生成编号

$projectId = 1; // 当前项目ID
$maxStageId = Stage::where('project_id', $projectId)->max('stage_id') ?? 0;

$stage = Stage::create([
    'project_id' => $projectId,
    'stage_id' => $maxStageId + 1,
    'name' => '测试阶段',
    'area' => 100.0,
    // 其他字段
]);

创建Land时生成编号

$projectId = 1;
$stageId = 1; // 当前阶段ID
$maxLandId = Land::where(['project_id' => $projectId, 'stage_id' => $stageId])->max('land_id') ?? 0;

$land = Land::create([
    'project_id' => $projectId,
    'stage_id' => $stageId,
    'land_id' => $maxLandId + 1,
    'cultivation_date' => now()->toDateString(),
    // 其他字段
]);

内容的提问来源于stack exchange，提问作者Ahmed Deghady