Interchangeably unique fields in ecto and/or postrgres?

Phoenix Forum Questions / Help
1

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)

and

(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

EDIT

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

2

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

1 Like
3

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

1 Like
4

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

5

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

6

I want to achieve the following using ecto:

7

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

1 Like