CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
Arya
Arya

Reputation: 1469

How to avoid SQLAlchemy IntegrityError on uniqueness constraint when swapping unique values

I have SQLAlchemy models as follows (abstracted away from the actual implementation)

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)

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):

    children_to_update = session.query(models.Child).filter(
        models.Child.parent_id == parent_id,
        models.Child.order <= new_order,
        models.Child.order > 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()

Upvotes: 1

Views: 339

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

To make this work, you first need to make your unique constraint on (parent_id, order) deferrable.

Then you need to defer the constraint before your query by sending set constraints <constraint name|all> deferred;

The deferred constraint will be checked automatically on commit.

Upvotes: 3

Related Questions