Question 260014 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search