Django项目注册触发IntegrityError：validation表id字段非空约束违规

错误信息 #

django.db.utils.IntegrityError: null value in column "id" of relation "validation" violates not-null constraint
DETAIL:  Failing row contains (null, 7, 839510, 0, 2024-10-29 19:51:34.057435+00, f, null).

使用pgAdmin管理PostgreSQL数据库时，创建Validation对象时持续触发上述非空约束错误。

相关代码 #

views.py #

from django.shortcuts import redirect, render  
from django.contrib.auth import login, authenticate  
from django.core.mail import send_mail  
from django.utils import timezone  
from .forms import UserRegistrationForm, UserLoginForm, VerificationForm  
from .models import Validation  
import random  # 补充原代码遗漏的导入

# 用户注册视图  
def register_view(request):  
    if request.method == 'POST':  
        form = UserRegistrationForm(request.POST)  
        if form.is_valid():  
            user = form.save()  
            login(request, user)  
            token = random.randint(100000, 999999)  
            Validation.objects.create(user=user, token=token,  
                                      expired=timezone.now() + timezone.timedelta(hours=1))  
            send_mail(  
                '你的验证码',  
                f'使用以下代码验证账号：{token}',  
                settings.DEFAULT_FROM_EMAIL,  
                [user.email],  
            )  
            return redirect('verification')  
    else:  
        form = UserRegistrationForm()  
    return render(request, 'registration/register.html', {'form': form})  

# 验证视图  
def verify_view(request):  
    if request.method == 'POST':  
        form = VerificationForm(request.POST)  
        if form.is_valid():  
            token = form.cleaned_data['token']  
            try:  
                validation = Validation.objects.get(user=request.user)  
                if validation.token == int(token) and validation.expired >= timezone.now():  
                    validation.validation_status = True  
                    validation.save()  
                    return redirect('profile')  
                else:  
                    # 处理验证失败逻辑
                    pass  
            except Validation.DoesNotExist:  
                pass  
    else:  
        form = VerificationForm()  
    return render(request, 'registration/verify_email.html', {'form': form})

models.py #

# 创建模型
from django.db import models
from django.conf import settings
from django.utils import timezone
from datetime import timedelta
import uuid
import os
from django.utils.text import slugify
from django.contrib.auth.models import User

class UserProfile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    status = models.CharField(max_length=50, null=True, blank=True)
    company_name = models.TextField(null=True, blank=True)
    logo = models.ImageField(upload_to='logos/', null=True, blank=True)
    validation = models.BooleanField(null=True, blank=True)

    def __str__(self):
        return self.user.username

    
class Validation(models.Model):
    id = models.BigAutoField(primary_key=True)
    user = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)  # 关联User模型
    token = models.BigIntegerField()
    tries = models.IntegerField(default=0)
    expired = models.DateTimeField(default=timezone.now)  # 默认当前时间
    validation_status = models.BooleanField(default=False)
    validation_date = models.DateTimeField(null=True, blank=True)

    def save(self, *args, **kwargs):
        if not self.id:
            self.expired = timezone.now() + timedelta(minutes=10)
        super().save(*args, **kwargs)

    def check_expiry_and_increment_tries(self):
        if timezone.now() > self.expired:
            self.tries += 1
            self.expired = timezone.now() + timedelta(minutes=10)  # 重置过期时间
            self.save()

    def generate_verification_code(self):
        self.token = uuid.uuid4().int >> 64  # 生成大整数token
        self.expired = timezone.now() + timedelta(minutes=10)
        self.save()

    def is_verification_code_valid(self):
        return timezone.now() <= self.expired

    class Meta:
        db_table = 'validation'

class Link(models.Model):
    id = models.BigAutoField(primary_key=True)
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    title = models.TextField()
    url = models.TextField()
    logo = models.TextField(null=True, blank=True)
    clicks = models.BigIntegerField(default=0)

    def save(self, *args, **kwargs):
        is_new = not self.pk
        old_logo = None

        if self.pk:
            old_logo = Link.objects.filter(pk=self.pk).first().logo

        super().save(*args, **kwargs)

        # 处理logo文件保存和旧文件删除
        if self.logo and is_new:
            file_extension = os.path.splitext(self.logo)[1]
            new_filename = f"{self.user.username}_{self.id}_{slugify(self.title)}{file_extension}"
            user_folder = os.path.join('logos/', self.user.username)
            new_path = os.path.join(user_folder, new_filename)

            if not os.path.exists(user_folder):
                os.makedirs(user_folder)

            # 保存新logo路径到数据库
            self.logo = new_path
            super().save(update_fields=['logo'])

        # 替换后删除旧logo
        if old_logo and old_logo != self.logo:
            try:
                old_file_path = os.path.join(settings.MEDIA_ROOT, old_logo)
                if os.path.exists(old_file_path):
                    os.remove(old_file_path)
            except Exception as e:
                print(f"删除旧文件出错: {e}")

    class Meta:
        db_table = 'links'

forms.py #

from django import forms  
from django.contrib.auth.models import User  
from django.contrib.auth.forms import UserCreationForm, AuthenticationForm  
from django.core.exceptions import ValidationError  
from .models import Validation   

# 用户登录表单  
class UserLoginForm(AuthenticationForm):  
    username = forms.CharField(max_length=254, required=True)  
    password = forms.CharField(label="密码", strip=False, widget=forms.PasswordInput)  

    class Meta:  
        model = User  
        fields = ['username', 'password']  

# 验证表单  
class VerificationForm(forms.Form):  
    token = forms.CharField(max_length=6, required=True, label="验证码")  

    def clean_token(self):  
        token = self.cleaned_data['token']  
        if not token.isdigit() or len(token) != 6:  
            raise ValidationError("验证码格式无效。")  
        return token  

# 用户注册表单  
class UserRegistrationForm(UserCreationForm):  
    first_name = forms.CharField(max_length=30, required=True)  
    last_name = forms.CharField(max_length=30, required=True)  
    email = forms.EmailField(required=True)  

    class Meta:  
        model = User  
        fields = ['username', 'first_name', 'last_name', 'email', 'password1', 'password2']  

    def clean_email(self):  
        email = self.cleaned_data['email'].lower()  
        if User.objects.filter(email=email).exists():  
            raise ValidationError("该邮箱已注册。")  
        return email

数据库表信息 #

• validation表：id列为bigint类型主键，但未配置自增序列（插入时无法自动生成id值）
• links表：id列同样为bigint主键，自增配置正常（无相关错误）

问题分析与解决方案 #

核心原因 #

PostgreSQL中BigAutoField依赖自增序列生成主键值，若validation表的id列未关联对应序列，Django插入数据时无法自动生成id，触发非空约束错误。

解决步骤 #

1. 手动修复数据库表结构
   登录pgAdmin执行以下SQL，为id列添加自增序列：

   -- 创建序列
   CREATE SEQUENCE validation_id_seq;
   -- 关联序列到id列
   ALTER TABLE validation ALTER COLUMN id SET DEFAULT nextval('validation_id_seq');
   -- 设置序列起始值（若表中有数据，取现有最大id+1）
   SELECT setval('validation_id_seq', COALESCE((SELECT MAX(id) FROM validation), 0) + 1);
   

2. 简化模型定义（推荐）
   Django默认会自动为模型创建BigAutoField类型的主键id，无需手动声明。删除Validation模型中的id = models.BigAutoField(primary_key=True)，让Django自动处理主键：

   class Validation(models.Model):
       # 移除手动id定义
       user = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
       # 其他字段保持不变
   

   执行迁移命令更新数据库：

   python manage.py makemigrations
   python manage.py migrate
   

3. 修复视图遗漏导入
   原代码中使用random.randint但未导入random模块，需补充import random避免引发其他错误。

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