Check a complex unique constraint with Ecto

Question

In my Ecto schema, I have a User who has_many Items. An item has a boolean field active:

User module:

  schema "users" do
    field :name, :string
    has_many :items, Item
  end

Item module:

  schema "items" do
    field(:active, :boolean)
    belongs_to(:user, User)
  end

How can I ensure that a User has at most one item active? Thus, if a user already has an active item, the insertion of another active item should result in an error. The insertion of a new item with active: false should succeed, though.

It seems that exclusion_constraint could be used for that, but I haven't found any docs showing how to do so.

Answer 1

You didn't describe your data model in any detail, but you can easily enforce such a condition on the database level if items has a foreign key to users (let's call it user_id). Then all you need is this partial unique index:

CREATE UNIQUE INDEX ON items (user_id) WHERE active;

Answer 2

I'm guessing from the tag that you're pointed at a Postgresql DB. If so and if you've got the correct exclusion constraint coded then you can run any arbitrary SQL you want with Ecto.Adapters.SQL.query/4 All you should need to do is to run the code to create the constraint once (probably in your seeds.exs script) and that should do it.