How would I write a migration to add the following unique constraint to my database?
Given the table below, check that when status == "active", the combination of fantasy_team_id and position are unique. In other words, a fantasy team can’t have more than one of any position if the status is “active”.
table:
roster_positions
column_names:
fantasy_team_id
position
status
From the Ecto Migration docs
https://hexdocs.pm/ecto/Ecto.Migration.html#constraint/3
I went to the PostresQL docs
I got to the code below, but I’m not sure exactly how to write the migrations since it looks like constraint/3 is used for CHECK. Also, I’m not sure how to apply the constraint only when the status is “active”.
ALTER TABLE roster_positions (
UNIQUE (fantasy_team_id, position)
);
Does anyone have any tips or recommended resources to research this some more? Thanks!
Axel
