Ecto - Creating a constraint where only one column is not null

Constraints

As shown in this SO question, to satisfy constraints 1 & 2 you could use something like:

create constraint(:table, :valid_pair, check: " ( ( CASE WHEN column_a IS NULL THEN 0 ELSE 1 END + CASE WHEN column_b IS NULL THEN 0 ELSE 1 END + CASE WHEN column_c IS NULL THEN 0 ELSE 1 END + CASE WHEN column_d IS NULL THEN 0 ELSE 3 END + CASE WHEN column_e IS NULL THEN 0 ELSE 3 END + CASE WHEN column_f IS NULL THEN 0 ELSE 3 END ) = 4 )") 

There's some ambiguity with constraint 3, do you mean absolutely only once can column_a and column_f could be together, or that the pairing of their values must be unique?

For [column_a, column_b, column_c, column_d, column_e, column_f]: Would both these transactions be valid or only one?

[1, NULL, NULL, NULL, NULL, NULL, 1]

[1, NULL, NULL, NULL, NULL, NULL, 2]

If the latter, then:

create unique_index(:table, [:column_a, :column_b, :column_c, :column_d, :column_e, :column_f], name: :unique_pairing) 

Changesets

For constraint 1 & 2, I would do two custom validations that count the number of non-nil values from each subset of columns. These validations are done prior to hitting the database. Good write up: More Custom Validations for Ecto Changesets

 changeset ... |> validate_column_sets([column_a, column_b, column_c], :column_set_a) |> validate_column_sets([column_d, column_e, column_f], :column_set_b) defp validate_column_sets(changeset, fields, key) do case changeset.valid? do true -> case Enum.count(fields, fn field -> get_field(changeset, field) != nil end) do 1 -> changeset _ -> add_error(changeset, key, "Only one column in this set can be nil") _ -> changeset end end 

For constraint 3 you could keep things simple and use unique_constraint/3 with an arbitrary column. See an discussion here: Ecto Changeset unique_constraint/3 for clustered index

|> unique_constraint(:column_a, name: :letters_unique_index)

Hope that gets you on the right track!