[Sqlalchemy-tickets] [sqlalchemy] #2765: Cascading with "delete-orphan" will insert new children, before deleting removed children, causing unique constraint violations

[sqlalchemy <mi...@zz...>]

#2765: Cascading with "delete-orphan" will insert new children, before deleting
removed children, causing unique constraint violations
-------------------------------------+-------------------------------------
 Reporter:  logandk                  |           Owner:  zzzeek
     Type:  defect                   |          Status:  new
 Priority:  medium                   |       Milestone:
Component:  orm                      |        Severity:  no triage selected
 Keywords:                           |  yet
  orm,delete,orphan,unique,constraint|  Progress State:  awaiting triage
-------------------------------------+-------------------------------------
 This issue occurs under the following circumstances:
 * When a relationship has the cascade property set to "all, delete-
 orphan",
 * There is a UniqueConstraint set on the child model
 * Simultaneously deleting and creating new child objects, through the
 backref on the parent, that have common values in the unique fields

 I believe this is a bug, and that it can be fixed by first deleting the
 removed children, followed by insertion of new children.

 The following example recreates the issue on SQLAlchemy 0.8.1.

 {{{
 from sqlalchemy import create_engine
 from sqlalchemy import Column, Integer, String, Float, UniqueConstraint,
 ForeignKey
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import sessionmaker, relationship, backref
 from sqlalchemy.orm.dependency import OneToManyDP, attributes

 Base = declarative_base()

 class ParentEntity(Base):
     __tablename__ = 'parent'

     id = Column(Integer, primary_key=True)
     name = Column(String)

 class ChildEntity(Base):
     __tablename__ = 'child'

     __table_args__ = (UniqueConstraint('parent_id', 'z',
 name='child_uk'),)

     id = Column(Integer, primary_key=True)
     parent_id = Column(Integer, ForeignKey('parent.id'), nullable=False)
     z = Column(Float)

     parent = relationship("ParentEntity",
                           backref=backref("children",
                                           cascade="all, delete-orphan"))

 engine = create_engine('sqlite://', echo=True)
 Session = sessionmaker(bind=engine)

 sess = Session()
 Base.metadata.create_all(engine)

 p = ParentEntity(name='Datum')
 p.children = [ChildEntity(z=2.5), ChildEntity(z=3.5), ChildEntity(z=5.5)]
 sess.add(p)
 sess.commit()

 # Remove all existing children and create new, one of which is identical
 to a previously existing child
 p.children = [ChildEntity(z=4.0), ChildEntity(z=5.5)]
 sess.commit()
 }}}

 Running this example produces the following error:

 {{{
 sqlalchemy.exc.IntegrityError: (IntegrityError) columns parent_id, z are
 not unique u'INSERT INTO child (parent_id, z) VALUES (?, ?)' (1, 5.5)
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2765>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python