Interchangeably unique fields in ecto and/or postrgres?

I have a table with three fields:
user_id, option_a and option_b

so for example:

user_id: 1
option_a: 2
option_b: 3

I don’t want the user to be able to insert:

(1, 2, 3)


(1, 3, 2)

at the same time because 2, 3 is the same as 3, 2

in other words: user_id, (option_a and option_b) should be unique such that option_a and option_b can be interchanged without affecting the uniqueness


in other words I want to achieve the following using ecto:

You probably want a database constraint and a matching check_constraint ecto validation. You can define the constraint in a migration.

1 Like

I’d recommend a separate options table with proper constraints. I see so many naively denormalized data models these days.

1 Like

What would the proper constraints be?
I don’t want (user x) to choose the same two options twice (regardless of their order)

table with columns user_id, option_name, option_value
unique constraint on (user_id, option_value) and (user_id, option_name)

I want to achieve the following using ecto:

I was responding to your question about normalizing your data to avoid having to do that.

1 Like