<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">This issue occurs under the following circumstances:</p>
<ul dir="auto">
<li>When a relationship has the cascade property set to "all, delete-orphan",</li>
<li>There is a UniqueConstraint set on the child model</li>
<li>Simultaneously deleting and creating new child objects, through the backref on the parent, that have common values in the unique fields</li>
</ul>
<p dir="auto">I believe this is a bug, and that it can be fixed by first deleting the removed children, followed by insertion of new children.</p>
<p dir="auto">The following example recreates the issue on SQLAlchemy 0.8.1.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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(&quot;ParentEntity&quot;,
                          backref=backref(&quot;children&quot;,
                                          cascade=&quot;all, delete-orphan&quot;))

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()"><pre class="notranslate"><code class="notranslate">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()
</code></pre></div>
<p dir="auto">Running this example produces the following error:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlalchemy.exc.IntegrityError: (IntegrityError) columns parent_id, z are not unique u'INSERT INTO child (parent_id, z) VALUES (?, ?)' (1, 5.5)"><pre class="notranslate"><code class="notranslate">sqlalchemy.exc.IntegrityError: (IntegrityError) columns parent_id, z are not unique u'INSERT INTO child (parent_id, z) VALUES (?, ?)' (1, 5.5)
</code></pre></div>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/2765/echo_out.txt">echo_out.txt</a></p>
<p dir="auto">When I try to compile a query with datetime parameters using <code class="notranslate">literal_binds</code>, e.g.:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, DateTime
from sqlalchemy.orm import sessionmaker
from datetime import datetime

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
class M(Base):
    __tablename__ = 'M'
    d = Column(DateTime, primary_key=True)

Session = sessionmaker()

print(Session().query(M).filter(
    M.d &lt; datetime.now()
).statement.compile(
    compile_kwargs={'literal_binds': True}
))"><pre class="notranslate"><code class="notranslate">from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, DateTime
from sqlalchemy.orm import sessionmaker
from datetime import datetime

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
class M(Base):
    __tablename__ = 'M'
    d = Column(DateTime, primary_key=True)

Session = sessionmaker()

print(Session().query(M).filter(
    M.d &lt; datetime.now()
).statement.compile(
    compile_kwargs={'literal_binds': True}
))
</code></pre></div>
<p dir="auto">the engine gives me the following error</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="NotImplementedError: Don't know how to literal-quote value datetime.datetime(2019, 12, 25, 14, 26, 42, 756674)"><pre class="notranslate"><code class="notranslate">NotImplementedError: Don't know how to literal-quote value datetime.datetime(2019, 12, 25, 14, 26, 42, 756674)
</code></pre></div>
<p dir="auto">There probably should be a default representation for datetimes as an iso-formatted string in single quotes.</p>