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.