Creating unique_index for {:array, :string} field type

I want to create a uniq_index for two fields.
for example

create unique_index(:tags, [:store_id, :names])

and :names field is {:array, :string}

And I don’t want to create a duplicate tag that have same names for each store

[“organic”, “produce”] == [“produce”, “organic”] is false.

How can I create a unique index for this field in ecto migration? or Do I have to use raw sql using execute ?

Is the order of values in the names column significant? If not, you could sort them in some convenient order and then rely on the standard uniqueness behavior.

the order of values are not important.
Do you mean before insert to database, just sort items?

Yes. That would ensure that the two lists in your example both get stored as [“organic”, “produce”] for instance.

1 Like

Yes. That is right. It could be a simple solution

Probably not what you want to hear, but you can also consider normalizing your schemas so that the SQL constraints work naturally.