Unique constraint migration

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”.



From the Ecto Migration docs
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!

1 Like

So to add a unique constraint you put in something like create unique_index(:roster_positions, [:position]), but since you want a constraint with it, specifically to only have a unique index on parts where status is active then that would be added in a where clause, such as in create unique_index(:roster_positions, [:position], where: "status LIKE 'active'") or something like that, thus only rows where status is like “active” (or whatever constraint you want) will be included in this unique index, all others are ignored and will not have this unique constraint. :slight_smile:


Thanks, I’ll give this a try!

1 Like

And for note, if you want literally only the combination of the :fantasy_team_id and the :position to be unique and not only individual, then in my above example instead of [:position] you would have [:fantasy_team_id, :position] instead, or whatever combinations you want. :slight_smile:

1 Like

Thanks! Once I started implementing it, I realized I also needed to exclude rows in which position is ‘Unassigned’ which is the default value. Here is what I finally used:

  def up do
    create unique_index(:roster_positions, [:position, :fantasy_team_id],
      where: "status LIKE 'active' AND position != 'Unassigned'")

  def down do
    drop unique_index(:roster_positions, [:position, :fantasy_team_id],
      where: "status LIKE 'active' AND position != 'Unassigned'")

Thanks for the help!!


Wouldn’t it be better to do an exact comparison on status? If that field only has active/inactive states I suggest using a boolean, but if its more than that I suggest using ecto_enum as it will allow you to operate w/ obvious values like :active/:pending/:inactive but uses integers in the database. More efficient for the DB and cleaner data.


@mgwidmann, thanks for the suggestion. I hadn’t seen ecto_enum, but I’ll check it out

On using ecto_enum and if you are using PostgreSQL, I highly recommend setting up PostgreSQL enum type in the database itself, helps you to pack the data even tighter and makes dumps readable! :slight_smile: