title: How to avoid SQLAlchemy IntegrityError on uniqueness constraint when swapping unique values
body: <p>I have SQLAlchemy models as follows (abstracted away from the actual implementation)</p>
  <pre><code>class Parent():
      id = Column(postgresql.UUID, primary_key=True)
  
  class Child():
      id = Column(postgresql.UUID, primary_key=True)
      parent_id = (postgresql.UUID,sqlalchemy.ForeignKey(Parent.id), nullable=False, index=True)
      order = sa_schema.Column(postgresql.SMALLINT)
  </code></pre>
  <p>And I have a uniqueness constraint on parent_id and order, so the ordering of the children on the parent is unique. I want to write code to allow reordering of these children, for example if I have children A B C D E in that order and want to change the order of child B from 2 to 4, I change C from 3 to 2, and D from 4 to 3. All of this is working, but when I go to commit the transaction, I get a IntegrityError stating that one of the order/parent_id pairs already exist (it's a random one every time). I have already turned off auto flush, does anyone have any idea how I could accomplish this? Example code (obviously this only handles the case where the order is increasing):</p>
  <pre><code>    children_to_update = session.query(models.Child).filter(
          models.Child.parent_id == parent_id,
          models.Child.order &lt;= new_order,
          models.Child.order &gt; original_order,
      ).with_for_update(nowait=True).all()
      for child_to_update in children_to_update:
          child_to_update.order = child_to_update.order - 1
          session.add(child_to_update)
  
      original_child.order = new_order
      session.add(original_child)
      session.commit()
  </code></pre>