Cascading with "delete-orphan" will insert new children, before deleting removed children, causing unique constraint violations

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=3.5), ChildEntity(z=5.5)](ChildEntity(z=2.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=5.5)](ChildEntity(z=4.0),)
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)

Comments (5)