Is it possible to have Ecto unique_constraint with errors on multiple fields?

Phoenix Forum Questions / Help
, ,
1

If I have some fields in one of my database tables that can’t simultaneously have the same values, I would create an index and then put the unique_constraint in my changeset, right?

Lets say I have a table of people with fields first_name and last_name. Two people can have the same first name or the same last name, but not both, otherwise they’d be the same person (I know that is not how real life works, it is just an example to make my question less abstract).

I made a migration with create unique_index(:people, [:first_name, :last_name]) to create the index in database level.

Then in my changeset pipeline, I put unique_constraint([:first_name, :last_name]).

If now I try to add “Lucas Perez” twice, the second insert would return a changeset like:

action: :insert,
changes: %{first_name: "Lucas", last_name: "Perez"},
errors: [
  first_name: {"has already been taken", [constraint: :unique, constraint_name: "people_first_name_last_name_index"]}
],
valid?: false

And if I check the constraints of my changeset (changeset.constraints), I’d have something like this:

[
  %{
    constraint: "people_first_name_last_name_index",
    error_message: "has already been taken",
    ...
    }
]

So far so good, this is exactly what is documented here, no surprises.

But is it possible to have both first_name and last_name in the fields of my constraints? So that the errors list would have both fist_name and last_name?

I even tried to call the unique_constraint twice in my changeset pipeline, but it ends using the last one.

changeset
|> unique_constraint([:first_name, :last_name], name: :people_first_name_last_name_index)
|> unique_constraint([:last_name, :first_name], name: :people_first_name_last_name_index)

If I do this, the changeset.constraints will have both constraints, each with a different field, but after attempting to insert the same person twice, the changeset.errors list will only have the last_name field.

I thought about creating a custom validation, but it is not very good, since the indexes/constraints are only checked at the moment of the database modification, which protects against concurrency. Also, a custom validation would traverse the whole table everytime I create a changeset, and not just at the moment of the database modification.

Is it possible to achieve this? Thanks a lot in advance!

2

I guess the more common case is a unique constraint on something like (account_id, username) and that’s probably the reason it works that way.

I think you can easily detect the unique constraint violation and manually add the error to the other field.

If you’d want to do this in a generic way, then Ecto.Changeset.constraints should be useful.

1 Like
3

I have failed at this in the past as well but didn’t try much to overcome it.

Maybe a workaround would be to have a virtual field – e.g. full_name – and you can have a function that converts the first_name unique constraint violation to belong to full_name. And you can pipe it at the end of your changeset function(s). Should work.

1 Like
4

There‘s two things at play here. One is that ecto changeset API by default considers validations and their errors to belong to a single field. This is even true for constraints.

The second is that constraints are based on errors thrown in the database. So while you can have multiple errors based on validations on a changeset you can only ever have a single error reported from a constraint, given the db will just abort on the first error it encounters.

1 Like
5

First of all, thanks! So many responses, and very fast!

Yes, that is indeed an option. I did it, but in the end we decided to just use a custom name for the error key, like this:
unique_constraint(:custom_name, name: :people_first_name_last_name_index) and acted accordingly when this error key appeared.

If I understand correctly, this would have the same effect as a custom name to our constraint?

I see now, that is a good thing to have clear in my mind

This does makes sense with what I was seeing in my attempt to duplicate the unique_constraint call. Thanks for the clarifications!

Like I said, we decided to just use a custom name and be happy with it. Thanks for the responses, very insightful. :grin:

2 Likes
6

Have you considered pairing the above with unsafe_validate_unique/4 during the validation phase?

That way you can provide quicker feedback to users before they submit the form and then seeing that there is an issue with whatever you’re testing for uniqueness.

Standardizes your UX such that any obvious issues with the provided info always get immediate feedback, and the DB still has the final say on uniqueness to avoid race conditions.

1 Like