Unique Constraint - What am I doing wrong?

Questions / Help
1

I keep getting this error when I try to insert a record where I know the ticket does not already exist.

constraint error when attempting to insert struct:
* PRIMARY (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.

The changeset defined the following constraints:

  • unique_ticket (unique_constraint)

This is what my schema and changeset are defined as:

  schema "tickets" do
    field(:type, :integer, default: 1)
    field(:ticket, :string)
    field(:title, :string)
    field(:body, :string)
    belongs_to(:release, Ern.Pdata.Release)
  end

  def changeset(ticket, attrs) do
    ticket
    |> cast(attrs, [:release_id, :type, :ticket, :title, :body])
    |> validate_required([:release_id, :type, :ticket, :title])
    |> unique_constraint(:ticket, name: :unique_ticket)
  end

unique_index

I wouldn’t think this would be an issue but I’ll mention it anyway…
I manually defined the data structure in mySQL - I did NOT create a migration for the table so it doesn’t have a create unique_index() to refer to.

Any clues where I’m going wrong???

UPDATE #1: I’m reading the error as; ecto is really complaining about my PRIMARY key which is defined as an auto-incrementing id field. I’ve never had to define a changeset “unique_constraint” for an id field before, so why now?? I thought ecto automagically deals with id fields by itself!?

UPDATE #2: I went ahead and added a constraint for the auto-incrementing id field |> unique_constraint(:id, name: :PRIMARY) and that fixed my problem.

I’m still not understanding why, all of a sudden, ecto wants the unique constraint defined for the id field - I’ve built several other database apps using elixir and this is the first time it moaned at me about the table’s primary index (all my tables use auto-incrementing id fields)?

Thanks!

1 Like
2

This sounds a lot like the problem described here (note the linked question is for PG not MySQL):

2 Likes
3

Not really. It wasn’t that it discovered a duplicate key, its that ecto needed to be told, for some curious reason, that its own key field had a unique constraint on it.

In mySQL, you can’t have an auto-indexing column without it having a unique constraint on it (typically and automagically called the PRIMARY key).

The issue for me is why I suddenly, for this one table, had to tell ecto that it had a PRIMARY key field when, since ecto creates its own id fields, it should have already known about it.

This time I had to inform ecto about it when, in the past, I never had to do that before!?

4

Ecto doesn’t automatically handle uniqueness constraint violation for primary keys. You probably haven’t seen this particular issue in the past because it’s pretty uncommon to run into it - as you mentioned, primary keys are auto increment and usually you cannot set them from outside, so the likely reason to run into this constraint violation is to break the sequence and/or manually set the id (e.g. when importing stuff etc)

5

This time I had to inform ecto about it when, in the past, I never had to do that before!?

All unique_constraint does is change what happens when a constraint error is returned from the database - it tells Ecto to transform the SQL error into an error on the changeset.

If an insert triggers a uniqueness failure on the PRIMARY unique index, that means either:

  • there’s an explicit id value in the payload that’s already taken by another row
  • the AUTO_INCREMENT value for the table is too low, causing MySQL to generate id values that are already taken by another row
1 Like