On Wed, Aug 30, 2017 at 7:52 AM, Levon Saldamli <levon...@gmail.com> wrote: > 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?
You've hit a classic issue, which is known as the "DELETE before INSERT" problem: https://bitbucket.org/zzzeek/sqlalchemy/issues/2501/the-delete-before-insert-problem There is in fact a solution attached to that issue in the form of a new option that would be added to the mapper() where you provide a set of columns that need to be maintained as unique. The reason it hasn't found its way in yet is because it's not clear how well received this option would be, what the real demand is for it as it's a fair it of work, or how well it works in real world use cases - you can see in the comments that everyone who tries it out still has some other aspects of their problem it does not solve, and then for whatever reason seem to move on from it. I've yet to have someone say hey this solved my problem. This is partially because the workaround is pretty simple, you just detach the first object, do session.flush() to delete it, then attach the new one. Let me know which approach you'd like to proceed with. > > 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. -- 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.
