Possible to set a constraint for a set of columns?

Questions / Help
1

Hi everyone.

I have an issue where I need to set a constraint for any given set of values.

For example, if I have two columns, user_from and user_to I need to make sure if there is already a user_from with 1 and a user_to with 2 there can’t be a user_to with 1 and a user_from with 2. That is, 1 and 2 should be unique together among those columns.

This DB Stack Exchange post notes that it is possible in Postgres. But not sure how I would do this in Ecto.

2 
create unique_index("table", [:user_from, :user_to])
3

I already have that constraint. That works if I try to create with the same user_from and the same user_to. But in this case, if such a record is made I want to prevent another record being made with the user from user_from as the user_to and vice versa. That doesn’t happen, and the record is inserted.

4

Oh you need it to work both ways:

create unique_index("table", ["least(user_from, user_to)", "greatest(user_from, user_to)"])

Edit:
You can also always just use raw sql in migrations with execute(sql)

4 Likes
5

Perfect, thanks @LostKobrakai, just what I was looking for. I guess I have to live with this long constraint name: connect_invites_least_user_from_id__user_to_id_greatest_user_fr xD.

6

You can optionally customize the name via the opts on the unique_index function.
https://hexdocs.pm/ecto_sql/3.3.4/Ecto.Migration.html#unique_index/3

2 Likes