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

Questions / Help
,
1

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

since
[“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 ?

2

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.

3

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

4

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

1 Like
5

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

6

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