Laravel学生评分功能报错求助：student_id字段无默认值

我用Laravel开发学生管理系统，已完成学生和科目的CRUD功能，现在开发课后测评评分模块，让教师给学生打分。创建测评时触发以下SQL错误：

SQLSTATE[HY000]: General error: 1364 Field 'student_id' doesn't have a default value (Connection: mysql, SQL: insert into assessments (assessment_name, score, score_over, user_id, updated_at, created_at) values (Homework11, 30,24, 40, 1, 2023-04-07 18:02:42, 2023-04-07 18:02:42))

以下是相关代码：

AssessmentController 的 store 方法 #

public function store(StoreAssessmentRequest $request)
{
    $validated = $request->validated();

    // Create the assessment instance
    $assessment = new Assessment();
    $assessment->assessment_name = $validated['assessment_name'];
    $assessment->score = implode(',', $validated['score']);
    $assessment->score_over = $validated['score_over'];
    $assessment->user_id = auth()->user()->id;

    // Save the assessment instance to the database
    if (!$assessment->save()) {
        // handle error, for example:
        return back()->withInput()->withErrors(['error' => 'Failed to save assessment']);
    }

    $assessment_id = $assessment->id;

    // Save the subject for the assessment
    $subject = Subject::where('name', $validated['subject'])->firstOrFail();
    $assessment->subjects()->syncWithoutDetaching($subject->id);

    // Save the scores for each student
    foreach ($validated['score'] as $student_id => $score) {
        $student = Student::findOrFail($student_id);
        $assessment->students()->syncWithoutDetaching([
            $student->id => [
                'score' => $score,
                'subject_id' => $subject->id,
                'assessment_id' => $assessment_id,
                'student_id' => $student_id,
            ]
        ]);
    }

    return redirect()->route('assessments.index')->with('success', 'Assessment saved successfully.');
}

create.blade.php 学生分数录入部分 #

在录入学生分数时，从student表获取所有学生并生成分数输入框：

<div class="form-group">
    <h3>Enter Scores of students</h3>
     @foreach($students as $index => $student)
       <label for="score-{{ $student->id }}">{{ $student->firstname }} {{ $student->lastname }}</label>
       <input type="number" class="form-control" id="score-{{ $student->id }}" name="score[{{ $student->id }}]" max="100" required>
      @endforeach
 </div>

StoreAssessmentRequest 验证规则 #

public function rules(): array
{
    return [
        'assessment_name' => 'required',
        'score' => 'required|array',
        'score_over' => 'required|integer|min:0|max:100',
        'subject' => 'required|exists:subjects,name',
        'score.*' => 'required|integer|min:0|max:100',
    ];
}

错误原因分析 #

报错核心是assessments表的student_id字段没有默认值，且插入数据时未提供该字段的值。但从业务逻辑看，一个测评对应多个学生，student_id不应该出现在assessments主表中，应该通过中间关联表（如assessment_student）存储测评与学生的关联及对应分数。

解决方案 #

1. 修正数据库表结构
   检查assessments表的迁移文件，移除多余的student_id字段，重新执行迁移：

   // 示例迁移文件（仅保留必要字段）
   public function up()
   {
       Schema::create('assessments', function (Blueprint $table) {
           $table->id();
           $table->string('assessment_name');
           $table->integer('score_over');
           $table->foreignId('user_id')->constrained(); // 关联教师用户
           $table->timestamps();
       });
   }
   

   执行迁移：

   php artisan migrate:refresh --path=/database/migrations/xxxx_xx_xx_create_assessments_table.php
   

2. 移除冗余代码
   删除store方法中给assessment->score赋值的代码，因为学生分数应存在关联表而非主表：

   // 删掉这行
   // $assessment->score = implode(',', $validated['score']);
   

3. 优化关联表数据写入
   关联表assessment_student需包含student_id、assessment_id、score、subject_id字段，确保迁移文件正确：

   public function up()
   {
       Schema::create('assessment_student', function (Blueprint $table) {
           $table->foreignId('assessment_id')->constrained()->onDelete('cascade');
           $table->foreignId('student_id')->constrained()->onDelete('cascade');
           $table->foreignId('subject_id')->constrained();
           $table->integer('score');
           $table->primary(['assessment_id', 'student_id']); // 联合主键避免重复
       });
   }
   

   同时简化store方法中关联学生的代码，无需手动传assessment_id和student_id，框架会自动填充：

   foreach ($validated['score'] as $student_id => $score) {
       $assessment->students()->attach($student_id, [
           'score' => $score,
           'subject_id' => $subject->id
       ]);
   }
   

   （用attach替代syncWithoutDetaching更合适，因为新增测评时不存在重复关联）

4. 确保模型关联正确
   检查Assessment模型的关联定义：

   class Assessment extends Model
   {
       public function students()
       {
           return $this->belongsToMany(Student::class)
               ->withPivot('score', 'subject_id')
               ->withTimestamps();
       }
   
       public function subjects()
       {
           return $this->belongsToMany(Subject::class);
       }
   }
   

内容的提问来源于stack exchange，提问作者Gilles Ashley