Constraint error trying to insert changeset

Phoenix Forum Questions / Help
, ,
1

I made an umbrella app, set it up, created the tables and ran the migrations.

When I try to test it out to add a new item I get the error below.

It seems like it’s saying the primary key is not unique, however I checked the DB and the ID field is set to nextval('quotes_id_seq'::regclass) which should auto increment it.

What am I doing wrong here?

Also note: The first time I run the insertion it works, but then fails on subsequent runs. It’s like the ID isn’t incrementing in the background.

changeset:

  @required_fields ~w(quote)a
  @optional_fields ~w(quote_source quote_body)a

  def changeset(quote, params \\ %{}) do
    quote
    # Casts to correct types
    |> cast(params, @required_fields ++ @optional_fields)
    # Performs validation
    |> validate_required(@required_fields)
    |> unique_constraint(:quote)
  end

Migration that created the table:

  def change do
    create table(:quotes) do
      add(:quote, :text)
      add(:quote_source, :text)
      add(:quote_body, :text)

      timestamps()
    end
  end

> %Quote{} |> Quote.changeset(%{quote: "Quote" }) |> QuoteQueries.create

22:26:53.401 [debug] QUERY ERROR db=13.4ms queue=6.2ms idle=9683.6ms
INSERT INTO "quotes" ("quote","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id" ["Quote", ~N[2020-05-18 22:26:53], ~N[2020-05-18 22:26:53]]
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * quotes_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.

The changeset defined the following constraints:

    * quotes_quote_index (unique_constraint)

    (ecto 3.3.4) lib/ecto/repo/schema.ex:694: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir 1.10.2) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (ecto 3.3.4) lib/ecto/repo/schema.ex:679: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto 3.3.4) lib/ecto/repo/schema.ex:660: Ecto.Repo.Schema.apply/4
    (ecto 3.3.4) lib/ecto/repo/schema.ex:263: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
2

Hello and welcome,

This is the failing constraint…

because You set a unique constraint.

It’s not the id which is duplicated, it’s the quote field.

3

Removing the unique_constraint gives me:

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

    * quotes_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.

**The changeset has not defined any constraint.**

    (ecto 3.3.4) lib/ecto/repo/schema.ex:694: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir 1.10.2) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (ecto 3.3.4) lib/ecto/repo/schema.ex:679: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto 3.3.4) lib/ecto/repo/schema.ex:660: Ecto.Repo.Schema.apply/4
    (ecto 3.3.4) lib/ecto/repo/schema.ex:263: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
4

Did You reset the db after the change? Did You look at the existing records to see if there were duplicate?

5

Can you show the full schema?

6

Sure, below is everything related to this table

quote.ex:

defmodule Quotes.Quote do
  use Ecto.Schema
  import Ecto.Changeset

  schema "quotes" do
    field(:quote, :string)
    field(:quote_source, :string)
    field(:quote_body, :string)

    timestamps()
  end

  @required_fields ~w(quote)a
  @optional_fields ~w(quote_source quote_body)a

  def changeset(quote, params \\ %{}) do
    quote
    # Casts to correct types
    |> cast(params, @required_fields ++ @optional_fields)
    # Performs validation
    |> validate_required(@required_fields)

    # |> unique_constraint()
  end
end

quote_queries.ex

defmodule Quotes.QuoteQueries do
  import Ecto.Query

  alias Quotes.{Repo, Quote}

  def get_all() do
    Repo.all(from(p in Quote, order_by: [desc: p.inserted_at]))
  end

  def get_by_id(id) do
    Repo.get(Quote, id)
  end

  def create(post_changeset) do
    Repo.insert(post_changeset)
  end
end

Migration creating the table:

defmodule Quotes.Repo.Migrations.CreateTable do
  use Ecto.Migration

  def change do
    create table(:quotes) do
      add(:quote, :text)
      add(:quote_source, :text)
      add(:quote_body, :text)

      timestamps()
    end
  end
end
7

I had deleted a single record, but deleting everything has allowed me to fix it and insert data.

Thanks for helping me walk through this.

8

If You really want to ensure quote is unique, You should add a unique index in your migration, something like…

create unique_index(:quotes, [:quote])
1 Like