Create a unique index as a unique constraint

Questions / Help
,
1

I have a many_to_many table with a migration that looks like this:

    def change do
      create table(:drivers_drivers) do
        add(:child_id, references(:drivers, on_delete: :delete_all), null: false)
        add(:parent_id, references(:drivers, on_delete: :delete_all), null: false)
        add(:position, :integer, null: false)
      end

      create(unique_index(:drivers_drivers, [:parent_id, :child_id], name: "unique_parent_child"))
      create(unique_index(:drivers_drivers, [:parent_id, :position], name: "unique_parent_position"))
    end

This creates indexes in PostgreSQL that look like this:

Indexes:
    "drivers_drivers_pkey" PRIMARY KEY, btree (id)
    "unique_parent_child" UNIQUE, btree (parent_id, child_id)
    "unique_parent_position" UNIQUE, btree (parent_id, "position")

I found that I was unable to reference the unique indexes as a :conflict_target in a call to insert_all/3. I was expecting that I could provide the options on_conflict: :replace_all, conflict_target: {:constraint, :unique_parent_child}, but this results in the error ERROR 42704 (undefined_object): constraint "unique_parent_child" for table "drivers_drivers" does not exist.

I was able to fix this issue by adding the following to the migration:

    execute("""
    ALTER TABLE drivers_drivers 
    ADD CONSTRAINT "unique_parent_child" 
    UNIQUE USING INDEX "unique_parent_child";
    """)
    
    execute("""
    ALTER TABLE drivers_drivers 
    ADD CONSTRAINT "unique_parent_position" 
    UNIQUE USING INDEX "unique_parent_position";
    """)

It feels like I am doing something wrong. Is there a way of referencing indexes in the :conflict_target option without defining them in PostgreSQL as constraints? Or, is there a way of specifying in the migration that an index should also be a constraint, without having to execute SQL?

A supplementary question is how can I specify more than one constraint as the :conflict_target to insert_all? It seems to only accept a tuple, with a single constraint specified as the second element in the tuple.

2

Postgres makes a distinction between indexes and constraints. If you pass conflict_target: :unique_parent_child then it should work, without the constraint definitions.

You also cannot pass multiple constraints. You can however pass multiple field names and let postgres do the job of inferring it. Did you try passing conflict_target: [:parent_id, :child_id, :position]?

3 Likes
3

Thank you for your help

I have now removed the execute statements from the migration and I am no longer using the constraint tuple to provide the value of conflict_target. However I have not found a conflict_target value that gives me the desired result.

If I specify either:
conflict_target: [:parent_id, :child_id]
or
conflict_target: [:parent_id, :position]
then the unique index specified appears to be correctly inferred by PostgreSQL as unique_violation errors only occur for the other unspecified unique index.

I want both unique indexes to be inferred, but if I specify:
conflict_target: [:parent_id, :child_id, :position]
then I get the error:
ERROR 42P10 (invalid_column_reference): there is no unique or exclusion constraint matching the ON CONFLICT specification.

4

Oh, so it can only infer a single index. Do you know if what you are trying to achieve is possible at all in Postgres? What would be the equivalent in SQL?

2 Likes
5

I have been trying to figure that out, but without success, so I am not sure it is possible (although my SQL knowledge is limited, so it could also be that :blush:).

What I learnt about Postgres 9.6 - SQL Insert today (open to correction):

  1. INSERT permits a single ON CONFLICT clause to be specified;
  2. A conflict_target is optional for ON CONFLICT DO NOTHING and, if omitted, conflicts with all usable constraints and unique indexes are handled. However, a conflict_target must be provided for ON CONFLICT UPDATE;
  3. The conflict_target either performs unique index inference or names a constraint explicitly;
  4. When performing inference, all unique indexes that (without regard to order) contain exactly the conflict_target-specified columns/expressions are inferred.

So, to me, that sounds like I can either specify a single constraint explicitly or use inference, which can infer multiple constraints but only on a single set of columns/expressions. Therefore I cannot specify or infer constraints on both (parent_id, child_id) and (parent_id, position). If this is the case, then Ecto is allowing me to do everything that is possible in Postgres.

1 Like