Repo.update_all on a column with a unique constraint

Questions / Help
1

I want to increment a unique column order (that specifies the order of items), when inserting a new item into the middle somewhere.

from q in query,
  update: [inc: [order: 1]],
  where: q.order < ^origin and q.order >= ^target

This is in a Multi |> Repo.transaction but I’m getting a unique constraint error, even though if the update_all succeeds there should be no unique constraint violation.

Is there any good way to do this?

1 Like
3

You probably need to defer the constraint on the database level, this article might help: https://hashrocket.com/blog/posts/deferring-database-constraints

2 Likes
4

Thanks a bunch, that did solve my similar issue.

For the record, here’s how I got it to work with elixir. First with the migration (my initial constraint was a 2-column unique constraint):

defmodule MyApp.Repo.Migrations.ModifyUniqueOrderConstraint do
  use Ecto.Migration

  def up do
    execute("""
    DROP INDEX unique_order_index;
    """)

    execute("""
    ALTER TABLE things
    ADD CONSTRAINT unique_order unique (order, parent_id)
    DEFERRABLE INITIALLY IMMEDIATE;
    """)
  end

  def down do
    execute("""
    ALTER TABLE things
    DROP CONSTRAINT unique_order;
    """)

    execute("""
    CREATE UNIQUE INDEX unique_order_index
    ON things
    USING btree (order, parent_id);
    """)
  end
end

And here is the new order update after deleting a “thing”:

    Repo.transaction(fn ->
      Repo.query!("SET CONSTRAINTS unique_order DEFERRED")

      from(t in Thing,
        where: t.parent_id == ^thing.parent_id and t.order > ^thing.order
      )
      |> Repo.update_all(inc: [order: -1])
    end)
1 Like
5

FWIW this never had a solution marked. If I recall correctly, I just did it in two steps.