PostgreSQL非空约束报错：SQLAlchemy创建User时id为Null

错误详情 #

(pg8000.exceptions.DatabaseError) {'S': 'ERROR', 'V': 'ERROR', 'C': '23502', 'M': 'null value in column "id" of relation "User" violates not-null constraint', 'D': 'Failing row contains (null, string, string, string, string, string, CA, string, string, string, string, 0, Temp, Temp, Temp, t, 2024-06-24 11:55:55.40777, 2024-06-24 11:55:55.40777, t).', 's': 'public', 't': 'User', 'c': 'id', 'F': 'execMain.c', 'L': '2009', 'R': 'ExecConstraints'}

背景 #

• 使用SQLAlchemy编写POST请求创建User表条目，建立State（父表）与User（子表）的一对多关系后触发该错误
• 未建立关系前，数据库会自动生成user_id；建立关系后，id字段传入null，违反非空约束
• 曾尝试将id列设置为Column(Integer, primary_key=True, nullable=True)，不符合规范且无效

相关代码 #

models.py #

class State(Base):
    __tablename__ = 'state'

    state_code = Column(String(2), primary_key=True)
    state_Name = Column(String(150), nullable=False)

    #relationship 
    # user_id = Column(Integer, ForeignKey('User.user_ID'))
    children = relationship("User", back_populates="parent")
    
class User(Base):
    __tablename__ = 'User'

    id = Column(Integer, primary_key=True)
    ### - UI
    first_name = Column(String(50))
    last_name = Column(String(50))
    address_line_1 = Column(String(100))
    address_line_2 = Column(String(100))
    city = Column(String(50))
    
    zip = Column(String(10))
    login_name = Column(String(100))
    email = Column(String(100), nullable=False)
    mobile_phone_num = Column(String(15))
    grade_level = Column(Integer)
    has_adhd = Column(Boolean)
    ### - UI

    ### - Google Auth
    google_id = Column(String(100))
    google_name = Column(String(100))
    google_profile_url = Column(String(255))
   

    is_active = Column(Boolean, nullable=False) #Default is True
    creation_date = Column(DateTime, nullable=False) # Current date user logs in, hardcoded
    last_update_date = Column(DateTime, nullable=False) # Current date user logs off, hardcoded
    
    #Relationships
    state_code = Column(String(2), ForeignKey('state.state_code'))
    parent = relationship("State", back_populates="children")

main.py #

##Schema##
class StateBase(BaseModel):
    state_name: str
class UserBase(BaseModel):
    first_name: str
    last_name: str
    address_line_1: str
    address_line_2: str
    city: str
    state_code: str
    zip: str
    login_name: str
    email: str
    mobile_phone_num: str
    grade_level: int
    has_adhd: bool

    google_id: str
    google_name: str
    google_profile_url: str
    
    is_active: bool
    creation_date: datetime
    last_update_date: datetime

## Cognifin
@app.post("/State")
async def create_state(state:StateBase, db: db_dependency):
    db_state = models.State(
        State_name = state.State_name
        )
    db.add(db_state)
    db.commit()
    db.refresh(db_state)

@app.post("/User")
async def create_user(user:UserBase, db: db_dependency):
    
    db_user = models.User(
        ##UI Elements
        first_name = user.first_name,
        last_name = user.last_name,
        address_line_1 = user.address_line_1,
        address_line_2 = user.address_line_2,
        city = user.city,
        state_code = user.state_code,
        zip = user.zip,
        login_name = user.login_name,
        email = user.email,
        mobile_phone_num = user.mobile_phone_num,
        grade_level = user.grade_level,
        has_adhd = user.has_adhd,

        ### - Google Auth
        google_id = "Temp",
        google_name = "Temp",
        google_profile_url = "Temp",
        ##UI backend
        is_active = True,
        creation_date = datetime.now(),
        #DDBL CHECK WITH KRISHNA
        last_update_date = datetime.now()
        )
    db.add(db_user)
    db.commit()
    db.refresh(db_user)

解决方案 #

1. 显式指定主键自动递增 #

修改User模型的id字段，添加autoincrement=True（确保SQLAlchemy明确告知数据库自动生成主键值）：

id = Column(Integer, primary_key=True, autoincrement=True)

2. 检查并修复数据库序列 #

执行以下SQL检查User表id字段的默认值配置：

SELECT column_default FROM information_schema.columns WHERE table_name = 'User' AND column_name = 'id';

如果返回结果为空或不是序列自增语句，手动创建并关联序列：

CREATE SEQUENCE user_id_seq OWNED BY "User".id;
ALTER TABLE "User" ALTER COLUMN id SET DEFAULT nextval('user_id_seq');

3. 同步模型与数据库表结构 #

• 若使用Alembic迁移工具，执行以下命令更新表结构：

  alembic revision --autogenerate -m "fix user id autoincrement"
  alembic upgrade head
  

• 未使用迁移工具的话，删除现有User表后重新创建，确保模型定义的属性同步到数据库。

4. 验证关系映射逻辑 #

确认State与User的关系配置无错误：

• State模型中的children = relationship("User", back_populates="parent")是正确的一对多配置
• User模型中的state_code外键关联正确，未干扰主键的自动生成逻辑

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