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