Flask SQLAlchemy中User与Currency默认货币一对一关系实现问题

Hey there! Let's break down your questions and find practical, clean solutions for your Flask-SQLAlchemy setup.

You’ll want two layers of protection to ensure this constraint holds: application-level validation (for immediate feedback) and optional database-level checks (for robustness):

Application-level validation #

Add a setter method to the User model to enforce that the default currency belongs to the user:

class User(UserMixin, db.Model):
    # ... existing fields
    currency_default_choice = db.Column(db.Integer, db.ForeignKey('currency.id', ondelete='SET NULL'))

    @property
    def currency_default(self):
        return self._currency_default

    @currency_default.setter
    def currency_default(self, currency):
        if currency.user_id != self.id:
            raise ValueError("Default currency must belong to the current user")
        self._currency_default = currency

You can also add similar checks in your FlaskForm validation logic when users select their default currency.

Database-level constraint (optional, for PostgreSQL/other supported DBs) #

If your database supports CHECK constraints, add a rule to the User table to enforce the link is to the user's own currency:

from sqlalchemy import text

class User(UserMixin, db.Model):
    # ... existing fields
    currency_default_choice = db.Column(
        db.Integer,
        db.ForeignKey('currency.id', ondelete='SET NULL'),
        db.CheckConstraint(
            text("EXISTS (SELECT 1 FROM currency WHERE id = currency_default_choice AND user_id = users.id)"),
            name="user_default_currency_belongs_to_user"
        )
    )

Note: MySQL ignores CHECK constraints natively, so stick to application-level checks there.

Filtered relationship setup #

You can also bake the constraint into the relationship’s primaryjoin to ensure only valid currencies are accessible via the relationship:

class User(UserMixin, db.Model):
    # ... existing fields
    currency_default_choice = db.Column(db.Integer, db.ForeignKey('currency.id', ondelete='SET NULL'))
    currency_default = db.relationship(
        'Currency',
        foreign_keys=[currency_default_choice],
        uselist=False,
        primaryjoin="and_(User.currency_default_choice == Currency.id, User.id == Currency.user_id)"
    )

Your intuition is mostly correct—the end result of a bidirectional relationship is the same regardless of which side you define it on, but there are small practical considerations:

• Semantic organization: Defining it on the User side makes more sense, since the default currency is a property of the user, not the currency itself.
• Backref clarity: When you define the relationship on User with backref, it automatically adds a reverse attribute to Currency (e.g., default_for_user). Defining it on Currency would add the reverse attribute to User, which feels less intuitive here.
• Parameter simplicity: Since the foreign key lives on the User table, defining the relationship here makes it easier to explicitly link the foreign key and avoid confusion.

As long as you set uselist=False (to enforce one-to-one) and correctly specify foreign_keys, both approaches work. The User side is just more aligned with your use case.

Root cause #

This error happens because SQLAlchemy is stuck resolving conflicting state updates between the User and Currency tables during deletion. Your current bidirectional relationship setup triggers mutual updates: when you clear user.currency_default_choice, SQLAlchemy tries to update both the User table and the reverse relationship on Currency, creating a circular loop in the transaction.

Fixes #

Here are two reliable ways to break the cycle:

Option 1: Use passive_deletes=True in the backref

Tell SQLAlchemy not to actively update the reverse relationship when the default is cleared:

class User(UserMixin, db.Model):
    # ... existing fields
    currency_default_choice = db.Column(db.Integer, db.ForeignKey('currency.id', ondelete='SET NULL'))
    currency_default = db.relationship(
        'Currency',
        foreign_keys=[currency_default_choice],
        uselist=False,
        backref=db.backref('default_for_user', uselist=False, passive_deletes=True)
    )

Remove the explicit currency_default relationship from the Currency class—let the backref handle the reverse link automatically.

Option 2: Adjust cascade settings

Modify the relationship’s cascade parameter to exclude delete operations that trigger the cycle:

currency_default = db.relationship(
    'Currency',
    foreign_keys=[currency_default_choice],
    uselist=False,
    cascade="save-update, merge, refresh-expire, expunge",
    backref=db.backref('default_for_user', uselist=False, cascade="save-update, merge, refresh-expire, expunge")
)

This removes delete and delete-orphan from the cascade rules, preventing automatic deletion of the currency when the default is cleared (which you probably don’t want anyway).

Putting it all together, here's the cleanest, most maintainable setup:

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, db.Sequence('user_id_seq'), primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(64), index=True, unique=True)
    pswd_hash = db.Column(db.String(128))
    # Renamed for clarity: foreign key to default currency
    currency_default_id = db.Column(db.Integer, db.ForeignKey('currency.id', ondelete='SET NULL'))
    
    # Relationships
    expenses = db.relationship('Expense', backref='user')
    currencies = db.relationship('Currency', backref='user', foreign_keys="Currency.user_id")
    currency_default = db.relationship(
        'Currency',
        foreign_keys=[currency_default_id],
        uselist=False,
        primaryjoin="and_(User.currency_default_id == Currency.id, User.id == Currency.user_id)",
        backref=db.backref('default_for_user', uselist=False, passive_deletes=True)
    )

class Currency(db.Model):
    id = db.Column(db.Integer, db.Sequence('currency_id_seq'), primary_key=True)  # Fixed sequence name to avoid confusion
    abbr = db.Column(db.String(10), db.ForeignKey('currency_official_abbr.abbr'))
    name = db.Column(db.String(64))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

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