I'm trying to have to objects with a one-to-one relationship, e.g. User and Address, with a UniqueConstraint for user.id in the address table (names changed from the real code). But when user.address is updated, the commit fails because the UniqueConstraint is broken, probably because SqlAlchemy inserts the new address into the table before removing the old one. If I set the address to None, commit and then set to a new address, it works fine. Is this a known issue or limitation, and is there a possibility that this will be supported in the future?
Full example with the failing commit below. SqlAlchemy version used is 1.1.13. The problem is reproducible with both sqlite and postgresql. from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy import ForeignKey from sqlalchemy.orm import relationship from sqlalchemy import UniqueConstraint engine = create_engine('sqlite:///:memory:', echo=True) Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) fullname = Column(String) address = relationship("Address", uselist=False, back_populates="user", cascade="all, delete-orphan") def __repr__(self): return "<User(name='%s', fullname='%s', id='%s')>" % ( self.name, self.fullname, self.id) class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email_address = Column(String, nullable=False) user_id = Column(Integer, ForeignKey('users.id')) user = relationship("User", back_populates="address") __table_args__ = (UniqueConstraint('user_id', name='_one_unique_address_per_user_uc'),) def __repr__(self): return "<Address(email_address='%s')>" % self.email_address Base.metadata.create_all(engine) ed_user = User(name='ed', fullname='Ed Jones') from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind=engine) ed_user.address = Address(email_address='f...@bar.com') session = Session() session.add(ed_user) session.commit() ed_user.address = Address(email_address='new_addr...@bar.com') session.add(ed_user) session.commit() -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.