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

Question

I have two sets of columns in my table:

A = ['column_a', 'column_b', 'column_c']

B = ['column_d', 'column_e', 'column_f']

I need to create a constraint where these conditions are met:

1. 1 column from A is not null and 1 column from B is not null.
2. Exactly 1 column from A is not null and same for B.
3. The pair of columns from A and B must be unique for every record.

So far, I've created a constraint that checks that at least one column is not null in each sets, but I do not know how to proceed from here:

create(constraint(:table, :valid_pair, check: "COALESCE(column_a, column_b, column_c) IS NOT NULL AND COALESCE(column_d, column_e, column_f) IS NOT NULL"))

Moreover, how do I implement this constraint in my changeset?

Answer 1

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!