向Django应用上传CSV文件时触发IntegrityError的排查求助

我有一个运行正常的QuizApp，包含Quiz、Question和Answer模型。为上传含题目与答案的CSV文件，新建了csvs应用，但上传时触发IntegrityError，文件无法上传至网站。

错误信息 #

IntegrityError at /something/admin/csvs/csv/add/
null value in column "quiz_id" of relation "csvs_csv" violates not-null constraint
DETAIL: Failing row contains (11, csvs/example_KLOIQJK.csv, 2022-09-15 05:46:25.666689+00, f, null)

相关代码 #

csvs/models.py #

class Csv(models.Model):
    file_name = models.FileField(upload_to="csvs")
    uploaded = models.DateTimeField(auto_now_add=True)
    activated = models.BooleanField(default=False)

csvs.forms.py #

from django import forms
from csvs.models import Csv

class CsvModelForm(forms.ModelForm):
    class Meta:
        model = Csv
        fields = ("file_name",)

csvs.views.py #

from django.shortcuts import render
from django.http import HttpResponse
from .forms import CsvModelForm

def upload_file_view(request):
    form = CsvModelForm(request.POST or None, request.FILES or None)
    if form.is_valid():
        form.save()
        form = CsvModelForm()
    return render(request, "csvs/import.html", {"form": form})

quiz.models.py #

from django.db import models
from django.contrib.auth import get_user_model
from django.urls import reverse, reverse_lazy
import uuid

class Quiz(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    title = models.CharField(max_length=200)
    short_description = models.CharField(max_length=500)
    resolution_time = models.PositiveIntegerField(
        help_text="Quiz Duration in minutes", default=15
    )
    number_of_questions = models.PositiveIntegerField(default=1)
    author = models.ForeignKey(
        get_user_model(),
        on_delete=models.CASCADE,
    )
    is_public = models.BooleanField(default=False)
    required_score_to_pass = models.IntegerField(
        help_text="Required score in %", default=50
    )

    def __str__(self):
        return f"Quiz title: {self.title}"

    def get_absolute_url(self):
        return reverse("quiz_detail", args=[str(self.id)])

    def get_absolute_url_edit(self):
        return reverse("quiz_edit", args=[str(self.id)])

    def get_absolute_url_questions_edit(self):
        return reverse("quiz_questions_edit", args=[str(self.id)])

    def get_absolute_url_delete(self):
        return reverse("quiz_delete", args=[str(self.id)])

    def get_absolute_url_my_quizes(self):
        return reverse("my_quiz")

    def get_absolute_url_answer_edit(self):
        return reverse("question_answer_edit")

    def get_absolute_url_create(self):
        return reverse("quiz_create")

    def get_absolute_url_play(self):
        return reverse("quiz_play", args=[str(self.id)])

class Question(models.Model):
    quiz = models.ForeignKey(
        Quiz,
        on_delete=models.CASCADE,
        related_name="questions",
    )
    content = models.CharField(max_length=255)
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)

    def __str__(self):
        return self.content

class Answer(models.Model):
    content = models.CharField(max_length=255)
    question = models.ForeignKey(
        Question,
        on_delete=models.CASCADE,
        related_name="answers",
    )
    is_correct = models.BooleanField(default=False)

    def __str__(self):
        return self.content

    def is_that_correct_answer(self):
        return self.is_correct

class Attempt(models.Model):
    user = models.ForeignKey(get_user_model(), on_delete=models.CASCADE)
    quiz = models.ForeignKey(Quiz, on_delete=models.CASCADE)
    score = models.SmallIntegerField(default=1)
    has_passed = models.BooleanField(default=False)
    attempt_date = models.DateTimeField(auto_now_add=True)

问题分析 #

你的Csv模型代码里没有定义quiz外键，但数据库的csvs_csv表却存在quiz_id字段且设置了非空约束。这说明之前的模型版本里有这个字段，后来你删掉了但没处理数据库迁移，或者手动修改过表结构，导致模型与数据库结构不一致。

解决方案 #

方案1：修复模型与数据库的一致性（推荐） #

如果你的CSV文件需要关联到某个Quiz，给Csv模型添加外键：

1. 更新csvs/models.py：

   from django.db import models
   from quiz.models import Quiz
   
   class Csv(models.Model):
       quiz = models.ForeignKey(Quiz, on_delete=models.CASCADE)  # 添加关联
       file_name = models.FileField(upload_to="csvs")
       uploaded = models.DateTimeField(auto_now_add=True)
       activated = models.BooleanField(default=False)
   

2. 更新表单，让用户选择对应的Quiz：

   # csvs/forms.py
   class CsvModelForm(forms.ModelForm):
       class Meta:
           model = Csv
           fields = ("quiz", "file_name")  # 包含quiz字段
   

3. 生成并应用迁移：

   python manage.py makemigrations csvs
   python manage.py migrate csvs
   

方案2：回滚迁移并重新生成（开发环境适用） #

如果是之前的迁移导致数据库残留字段，且不需要关联Quiz：

1. 回滚csvs应用的所有迁移（会清空表数据，生产环境勿用）：

   python manage.py migrate csvs zero
   

2. 删除csvs/migrations下的旧迁移文件（除了__init__.py）
3. 重新生成迁移并应用：

   python manage.py makemigrations csvs
   python manage.py migrate csvs
   

方案3：直接修改数据库（应急用，不推荐） #

如果确定不需要quiz_id字段，可直接修改数据库：

• PostgreSQL示例命令：

  -- 允许字段为空
  ALTER TABLE csvs_csv ALTER COLUMN quiz_id DROP NOT NULL;
  -- 或者删除字段
  ALTER TABLE csvs_csv DROP COLUMN quiz_id;
  

这种方式会导致模型与数据库结构不一致，后续迁移易出问题，尽量避免。

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