Search code examples
postgresqlelixirconstraintsecto

Check a complex unique constraint with Ecto

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.

Solution

  • 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;