[Python] How to avoid (or solve) unique constraint violation when inserting into database?

I have a table with a partial unique constraint where a customer_id can only have one is_primary flag set to True.

The behavior that I would like to achieve is: when I insert a row with is_primary=True and a customer_id that already has its is_primary flag set to True in the database, I would like to set that old entry to is_primary=False, and the new entry would be inserted with is_primary=True, not raising an IntegrityError.

I'm using flask-sqlalchemy with a postgresql database, is there any mechanism to do this easily or do I need to do it manually, first update old entry and then insert the new row?

class Customer_address(db.Model):
customer_id = db.Column(None, db.ForeignKey('customer.id'), nullable=False)
is_primary = db.Column(db.Boolean, nullable=False)
__table_args__ = (
Index(
'only_one_primary_address', # Index name
'customer_id', # Columns which are part of the index
unique=True,
postgresql_where=(is_primary) # The condition
),)

Continue reading...