Ecto: unique_constraint with name still raising exception instead of error

Questions / Help
#1

I am getting this red exception error despite the fact that I’m using unique_constraint/3:

** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * join_lessons_resources_pkey (unique_constraint)

If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `unique_constraint/3` on your changeset with the constraint
`:name` as an option.

I updated my migration to use a name:

  def change do
    drop unique_index(:join_lessons_resources, [:lesson_id, :resource_id])
    create unique_index(:join_lessons_resources, [:lesson_id, :resource_id], name: :lesson_resource_index)
  end

Then I updated my schema to use a name:

def changeset(join_lesson_resource, attrs) do
    join_lesson_resource
    |> cast(attrs, [:lesson_id, :resource_id])
    |> validate_required([:lesson_id, :resource_id])
    |> unique_constraint([:lesson_id, :resource_id], name: :lesson_resource_index)
  end

The code that is passing the ids to be checked is using a changeset:

join =
      JoinLessonResource.changeset(
        %JoinLessonResource{}, %{lesson_id: lesson_id, resource_id: resource_id})
    Repo.insert(join)

I emptied my database (which only had 4 entries so it was no big deal). I then entered a new entry and it worked fine. I then tried to enter the same entry (same lesson_id and same resource_id) and i got the error above. As far as I can tell, I’m doing everything correctly in terms of using unique_constraint/3.

Why is it raising an red exception with Ecto.ConstraintError instead of returning an error in my changeset??

1 Like
#2

You error says that the constraint that is failing is join_lessons_resources_pkey which is added automatically on primary keys. So since you haven’t added that unique constraint to your changeset (you’ve only added other ones), the constraint is not caught when it is violated.

So in your changeset you probably want to add a unique constraint for the primary key as well. It will look something like this if your primary key is :id:

    |> unique_constraint([:id], name: :join_lessons_resources_pkey)
#3

Thank you for the help @axelson!

My schema looks like this:

schema "join_lessons_resources" do
    field :lesson_id, :integer, primary_key: true
    field :resource_id, :integer, primary_key: true
  end

So I have a composite primary key. I don’t think unique_constraint [:id] will work, because my table does not have a primary key set to :id. It has two columns: lesson_id and resource_id and they are jointly primary keys. So for my migration and schema, I did the above composite primary key for the unique_constraint input.

#4

In psql (PostgreSQL: Documentation: 13: psql) (assuming you’re using postgres), what does the constraint on the table look like?

You can check by running \d join_lessons_resources in psql.

Also are you sure you re-created the right database? Perhaps your migration is not applied on the database you’re checking.

#5

Here’s what I get when I run \d join_lessons_resources:

sketch_links_dev=# \d join_lessons_resources
         Table "public.join_lessons_resources"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 lesson_id   | bigint |           | not null | 
 resource_id | bigint |           | not null | 
Indexes:
    "join_lessons_resources_pkey" PRIMARY KEY, btree (lesson_id, resource_id)
    "lesson_resource_index" UNIQUE, btree (lesson_id, resource_id)
Foreign-key constraints:
    "join_lessons_resources_lesson_id_fkey" FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE
    "join_lessons_resources_resource_id_fkey" FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE
#6

It looks like the primary index (which is also UNIQUE by definition) is what is raising:

    "join_lessons_resources_pkey" PRIMARY KEY, btree (lesson_id, resource_id)

So I’d get rid of the create unique_index(:join_lessons_resources, [:lesson_id, :resource_id], name: :lesson_resource_index) that you’ve added in your migration and just use the primary key index with join_lessons_resources_pkey. It might be possible to have ecto change the default primary key index name, but I’m not sure how you’d do that.

Does that make sense?

#7

If I understand correctly, what you’re saying is that by setting primary_key:true to both of those fields, I essentially already created a unique constraint on those two keys. So the unique_index in the migration and the unique_constraint in the schema is not necessary. And they are actually conflicting with the composite primary key.

But if I make these changes (remove unique_index in migration and unique_constraint in schema), will the changeset validate that a duplicate is not going in before the database raises an error? Or should I just manually do a query to check that there is no duplicate. I basically did that as a workaround for this issue:

# Keep Import here or it throws errors with the "select f" in render(assigns)
    import Ecto.Query
    query = from a_join in JoinLessonResource,
      where: a_join.lesson_id == ^lesson_id and
      a_join.resource_id == ^resource_id,
      select: a_join

    # Check that resource is not already in Lesson
    if Repo.exists?(query) do
      IO.puts "Already in library"
      {:noreply, socket
          |> put_flash(:info, "Resource is already in this Lesson!")
          |> push_redirect(to: socket.assigns.return_to)
        }

It just would be great if the changeset would validate this for me instead of having to do a query every time.

Hmmm … I guess I could just take the code above and write my own validate and throw that into the changeset. Is that the best thing to do?

#8

Do you consider classic id as a primary key? Can’t find the article, but somewhere read about recommendations using only it for primary keys in PG. I guess it will solve all your problems(with uniq index on lesson_id and resource_id)

#9

Yes, that is correct. Although I wouldn’t say that they are “conflicting”, it’s just that the non-primary one is duplicative and not providing any value (but still having a performance cost, along with being confusing)

I want to tweak how you’re thinking about this a tiny bit. I don’t think you should worry about “the database raising an error”, since detecting a conflict like that is something that the database is meant to do.

So by adding this to your changeset (slightly tweaked from my initial suggestion):
|> unique_constraint([:lesson_id, :resource_id], name: :join_lessons_resources_pkey)

The database will detect the conflict/duplicate lesson_id/resource_id pair, and raise an error. The Ecto changeset will then detect that error and return it to you nicely formatted.

Yes, this is something you can do in addition to adding the unique_constraint to the changeset. But it isn’t safe to do as the only uniqueness check because there are race conditions. Ecto provides Ecto.unsafe_validate_unique/4 to help with this use-case, although the use-case is primarily to provide early feedback, such as proving feedback to a user that is filling in a form (vs waiting until they submit the form)

3 Likes
#10

Thank you very much for the help and the detailed reply. Much appreciated since I’m new to this and trying to learn as much as possible! I’ll give this all a try. :slight_smile:

1 Like
#11

Glad to help! :blush:

2 Likes