Django项目添加员工时遇IntegrityError外键约束失败问题求助

问题描述 #

在Django项目中，通过自定义表单向数据库添加员工数据时，访问/add_emp路径触发IntegrityError，提示FOREIGN KEY constraint failed。已尝试删除db.sqlite3文件、迁移文件和__pycache__文件，但问题仍未解决。

相关代码 #

Models.py #

from django.db import models

# Create your models here.

class Department(models.Model):
    name = models.CharField(max_length=100,null=True)
    location = models.CharField(max_length=100)

    def __str__(self):
        return self.name

class Role(models.Model):
    name = models.CharField(max_length=100, null=True)

    def __str__(self):
        return self.name

class Employee(models.Model):
    first_name = models.CharField(max_length=100, null=False)
    last_name = models.CharField(max_length=100,null=True)
    dept = models.ForeignKey(Department, on_delete=models.CASCADE)
    salary = models.IntegerField(default=0,null=True)
    bonus = models.IntegerField(default=0,null=True)
    role = models.ForeignKey(Role, on_delete=models.CASCADE)
    phone = models.IntegerField(default=0,null=True)
    hire_date = models.DateField(null=True)

    def __str__(self):
        return "%s %s %s" %(self.first_name, self.last_name, self.phone)

views.py #

# Create your views here.
def index (request):
    return render(request,"index.html")

def all_emp (request):
    context={"emps":Employee.objects.all(),"roles":Role.objects.all(),"departments":Department.objects.all()}
    return render(request,"all_emp.html",context)

def add_emp(request):
    if request.method == 'POST':
        first_name = request.POST['first_name']
        last_name = request.POST['last_name']
        salary = int(request.POST['salary'])
        bonus = int(request.POST['bonus'])
        phone = int(request.POST['phone'])
        dept = int(request.POST['dept'])
        role = int(request.POST['role'])
        new_emp = Employee(first_name= first_name, last_name=last_name, salary=salary, bonus=bonus, phone=phone, dept_id=dept,role_id=role,hire_date = datetime.now())
        new_emp.save()
        return HttpResponse ("Employee added sucessfully")
    
    elif request.method=='GET':
        return render(request, 'add_emp.html')
    else:
        return HttpResponse("An Exception Occured! Employee Has Not Been Added")

def remove_emp (request):
    return render(request,"remove_emp.html")

def filter_emp (request):
    return render(request,"filter_emp.html")

HTML表单（add_emp.html） #

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Add Employee</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
</head>
  <body>
  <div class="container">
          <h1>Add An Employee!</h1>
      <hr>

<form action="/add_emp" method="post">
    {% csrf_token %}
    <label for="first_name">First Name</label>
    <br>
    <input type="text" id="first_name" name="first_name"><br>

    <label for="last_name">Last Name</label>
    <br>
    <input type="text" id="last_name" name = "last_name"><br>

    <label for="salary">Salary</label>
    <br>
    <input type="number" id="salary" name="salary"> <br>

    <label for="dept">Department</label>
    <br>
    <input type="number" id="dept" name="dept" ><br>

    <label for="role">Role</label>
    <br>
    <input type="number" id="role" name="role" ><br>

    <label for="bonus">Bonus</label>
    <br>
    <input type="number" id="bonus" name="bonus" ><br>

    <label for="phone">Phone Number</label><br>
    <input type="number" id="phone" name="phone"><br>

    <hr>
    <button type="submit" class="btn btn-primary">Submit</button>
</form>

  </div>

    <!-- Optional JavaScript; choose one of the two! -->

    <!-- Option 1: Bootstrap Bundle with Popper -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>

    <!-- Option 2: Separate Popper and Bootstrap JS -->
    <!--
    <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.10.2/dist/umd/popper.min.js" integrity="sha384-7+zCNj/IqJ95wo16oMtfsKbZ9ccEh31eOz1HGyDuCQ6wgnyJNSYdrPa03rtR1zdB" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.min.js" integrity="sha384-QJHtvGhmr9XOIpI6YVutG+2QOK9T+ZnN4kzFN1RtK3zEFEIsxhlmWl5/YESvpZ13" crossorigin="anonymous"></script>
    -->
  </body>
</html>

问题原因与修复步骤 #

核心原因 #

这个错误是因为你尝试关联的部门（Department）或角色（Role）在数据库中不存在，违反了外键约束——Employee表的dept和role字段必须关联已存在的Department或Role记录。

具体修复方案 #

1. 先向数据库添加基础数据
   必须先有Department和Role数据，才能添加关联的员工。通过Django Shell手动添加：

   python manage.py shell
   

   在Shell中执行（替换app为你的实际应用名称）：

   from app.models import Department, Role
   # 添加测试部门
   Department.objects.create(name="技术部", location="北京")
   # 添加测试角色
   Role.objects.create(name="开发工程师")
   

2. 优化表单，避免手动输入ID出错
   当前表单让用户手动输入部门/角色ID，极易输入不存在的ID，改成下拉选择框更可靠：

   • 修改add_emp视图的GET方法，传递部门和角色数据：

     def add_emp(request):
         if request.method == 'POST':
             # 原有逻辑不变
         elif request.method=='GET':
             # 传递部门和角色数据到前端
             context = {"departments": Department.objects.all(), "roles": Role.objects.all()}
             return render(request, 'add_emp.html', context)
         else:
             return HttpResponse("An Exception Occured! Employee Has Not Been Added")
     

   • 修改add_emp.html中的部门和角色输入部分：

     <label for="dept">Department</label>
     <br>
     <select id="dept" name="dept">
         {% for dept in departments %}
             <option value="{{ dept.id }}">{{ dept.name }}</option>
         {% endfor %}
     </select><br>
     
     <label for="role">Role</label>
     <br>
     <select id="role" name="role">
         {% for role in roles %}
             <option value="{{ role.id }}">{{ role.name }}</option>
         {% endfor %}
     </select><br>
     

3. 修正日期字段类型错误
   hire_date是DateField，但你用了datetime.now()（返回datetime对象），会导致类型不匹配，改成：

   # 先导入datetime
   from datetime import datetime
   
   # 在创建Employee时修改
   hire_date=datetime.now().date()
   

4. 添加异常捕获，给出明确提示
   在保存员工时捕获异常，避免崩溃并提示用户：

   from django.db import IntegrityError
   
   def add_emp(request):
       if request.method == 'POST':
           try:
               # 原有获取参数的逻辑
               new_emp = Employee(...)
               new_emp.save()
               return HttpResponse("Employee added successfully")
           except IntegrityError:
               return HttpResponse("错误：所选部门或角色不存在，请检查输入")
           except ValueError:
               return HttpResponse("错误：请输入正确的数字格式")
       # 后续逻辑不变
   

5. 重新执行正确的迁移流程
   之前删除迁移文件后，重新生成并执行迁移：

   python manage.py makemigrations
   python manage.py migrate
   

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