Unique constraint migration

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.

Thanks, I’ll give this a try!

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.

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'")
  end

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

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!