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 tha…

A partial unique constraint can do that as well. create unique_index(:table, [:user_id], where: "active = TRUE")

The easiest way to solve this is probably to store nil/NULL instead of false. Then you can use a regular unique index because it only applies to non-NULL values.

[image] wmnnd: The easiest way to solve this is probably to store nil/NULL instead of false. Adding onto this, you can coerce false to nil in your Item changesets to prevent accidentally writing a false. [image] LostKobrakai: A partial unique constraint can do that as well. create uniqu…

Thank you! I didn’t know that creating a unique index actually creates a constraint.

[image] christhekeele: Either way, after doing one of them you can add :active_item like so to your User schema for easy access: schema "users" do field :name, :string has_many :items, Item has_one :active_item, Item, where: [active: true] timestamps() end Nice, didn’t k…

Yep! IIRC it will even populate the active field for you using certain association helpers, tho I forget exactly which.

[image] LostKobrakai: A partial unique constraint can do that as well. create unique_index(:table, [:user_id], where: "active = TRUE") When making a partial index like this, consider if you’ll ever want to query for “every row for a user_id” in production. If so, make sure there’s also an in…

Elixir Programming Language Forum

Check a complex "unique" constraint with Ecto

Questions & Help Questions

sodapopcan October 10, 2023, 8:42pm 9

I’d never thought of that before, thanks!