Unique constraint migration

Questions / Help
1

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
https://www.postgresql.org/docs/9.4/static/ddl-constraints.html

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

1 Like
2

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:

6 Likes
3

Thanks, I’ll give this a try!

1 Like
4

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
5

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!!

3 Likes
6

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.

2 Likes
7

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

8

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:

3 Likes