I have a schema in which there are three primary keys and And I am setting one of these primary keys which is a
foreign key in the upsert
conflict options like this:
Repo.insert_all(Structnamet, list_of_maps, on_conflict: :replace_all, conflict_target: :id)
It gives me this error:
(Postgrex.Error) ERROR 42P10 (invalid_column_reference) there is no unique or exclusion constraint matching the ON CONFLICT specification
Which basically means the id doesn’t have unique constraint on it.
I assume that primary keys have unique constraint by default.
If i write a new migration like this it works:
drop_if_exists index("table_name", [:id])
But I don’t think this is a right approach to drop and create unique index on primary key.
So any suggestions will be appreciated?
If you have 3 primary keys then any individual one won’t have a unique constraint, you’d need to add it to that one manually (or just make that ‘one’ the primary key). If you have 3 primary keys then it’s the aggregate of those 3 that are uniqued, not each individual one.
Yep, you are dropping the primary key aggregate then creating a unique index for
:id (you really shouldn’t need to drop as there is probably not one created yet).
But no, this is not the right approach, and
:id is not the primary key (hence why no unique index), it is only part of the aggregate of the 3-tuple of the primary key.
For note, if you do have a composite primary key then you need to setup a foreign key to the aggregate, not a singular value. Last I tried Ecto did not make this easy even though PostgreSQL supports composite foreign key’s so I had to resort to SQL for it. It might be fixed in Ecto now though, unsure (I don’t really use its built in references/has_* and so forth anymore due to various limitations I’ve hit with them over time, so it’s just easier to do it manually for me again)…
Thanks that explains a lot.