如何确保SQLAlchemy关联表中同一用户与挑战仅存一条记录？

现有模型代码 #

from . import db
from flask_login import UserMixin
from sqlalchemy import text
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.associationproxy import AssociationProxy

import json

class Base(db.DeclarativeBase):
    #this will make the base class abstract, so it won't be mapped to a table.
    __abstract__ = True
    pass

class User(UserMixin, db.Model):
    __tablename__ = "usersTbl"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True) # primary keys are required by SQLAlchemy

    mileage = db.relationship('MileagePerChallenge', back_populates='user', lazy=True)
    challenges = association_proxy('mileage', 'challenge')

class Challenge(db.Model):
    __tablename__ = "challengesTbl"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)

    mileage = db.relationship('MileagePerChallenge', back_populates='challenge', lazy=True)
    users = association_proxy('mileage', 'user')

class MileagePerChallenge(db.Model):
    __tablename__ = "mileagePerchallengesTbl"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    userId = db.Column(db.Integer, db.ForeignKey('usersTbl.id'), index=True)
    challengeId = db.Column(db.Integer, db.ForeignKey('challengesTbl.id'), index=True)

    user = db.relationship('User', back_populates='mileage', lazy=True)
    challenge = db.relationship('Challenge', back_populates='mileage', lazy=True)

操作代码 #

执行以下代码时，即使是同一个chal_obj和user，关联表会生成重复记录：

chal_obj.users.append(user)

问题解析与解决方案 #

1. 你忽略的点

数据库和SQLAlchemy不会自动维护多对多关联的唯一性。当前MileagePerChallenge表仅以自增id作为主键，userId和challengeId的组合没有任何唯一性约束，所以每次执行append都会插入新的关联记录。

2. 是否为正常行为

这是正常行为。在没有显式唯一性约束的情况下，数据库允许相同的userId+challengeId组合重复存在；SQLAlchemy也不会主动查询现有关联来避免重复，而是直接生成新的插入语句。

3. 实现唯一关联的配置方法

有两种可靠的方式确保关联唯一：

方式一：添加联合唯一约束（保留自增id）

在MileagePerChallenge类中添加__table_args__来定义联合唯一约束：

class MileagePerChallenge(db.Model):
    __tablename__ = "mileagePerchallengesTbl"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    userId = db.Column(db.Integer, db.ForeignKey('usersTbl.id'), index=True)
    challengeId = db.Column(db.Integer, db.ForeignKey('challengesTbl.id'), index=True)
    
    # 新增联合唯一约束，确保userId和challengeId的组合唯一
    __table_args__ = (
        db.UniqueConstraint('userId', 'challengeId', name='_user_challenge_unique'),
    )
    
    user = db.relationship('User', back_populates='mileage', lazy=True)
    challenge = db.relationship('Challenge', back_populates='mileage', lazy=True)

方式二：使用复合主键（移除自增id）

直接将userId和challengeId设为复合主键，天然保证组合唯一：

class MileagePerChallenge(db.Model):
    __tablename__ = "mileagePerchallengesTbl"
    # 用userId和challengeId作为复合主键
    userId = db.Column(db.Integer, db.ForeignKey('usersTbl.id'), primary_key=True, index=True)
    challengeId = db.Column(db.Integer, db.ForeignKey('challengesTbl.id'), primary_key=True, index=True)
    
    user = db.relationship('User', back_populates='mileage', lazy=True)
    challenge = db.relationship('Challenge', back_populates='mileage', lazy=True)

额外代码层面优化（辅助避免重复）

如果需要在代码层面提前检查，可以先判断关联是否存在再执行添加：

if user not in chal_obj.users:
    chal_obj.users.append(user)

但这种方式无法应对并发写入场景，最可靠的还是依赖数据库层面的约束。

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