Ecto Changeset generates "id" can't be blank for autogenerated primary_key table

Hi all,

I have a phoenix project that is by default a binary_id project. But there are some legacy tables which I populate using a seed.exs script that inserts “id” explicitly as an integer, type :id

When I attempt to insert a new row WITHOUT explicitly specifying “id”, I get the changeset error “id can’t be blank”, even though the MediaMusic schema has an explicit @primary_key {:id, :id, autogenerate: true}

How do I get ecto to insert a new row and autogenerate the serial :id for me?

Here is the iex session:

iex(4)> attrs = %{uuid: Ecto.UUID.generate(), path: "a/path", language_id: "en", localizedname: "untitled", music_id: 1} 
%{
  path: "a/path",
  uuid: "f36904fd-5985-4ebb-95bc-6af83d8e7ca7",
  language_id: "en",
  localizedname: "untitled",
  music_id: 1
}
iex(5)> %Word.Schema.MediaMusic{} |> Word.Schema.MediaMusic.changeset(attrs) |> Repo.insert()
{:error,
 #Ecto.Changeset<
   action: :insert,
   changes: %{
     path: "a/path",
     uuid: "f36904fd-5985-4ebb-95bc-6af83d8e7ca7",
     language_id: "en",
     localizedname: "untitled",
     music_id: 1
   },
   errors: [id: {"can't be blank", [validation: :required]}],
   data: #Word.Schema.MediaMusic<>,
   valid?: false
 >}

Here is a gist of the files in question:

Thanks,
Michael

Remove your validate_required call for :id. If the id is autogenerated it won‘t exist yet at the time of validation.

1 Like

Hi, so I tried that and I get a not_null_violation:

iex(5)> %Word.Schema.MediaMusic{} |> Word.Schema.MediaMusic.changeset(attrs) |> Repo.insert()
[debug] QUERY ERROR db=7.6ms queue=4.3ms idle=600.5ms
INSERT INTO "mediamusic" ("path","uuid","language_id","localizedname","audio_urls","duration","size_bytes","view_count","media_format","media_urls","music_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) RETURNING "id" ["a/path", "d850d2e0-a79a-449b-a5f3-9a317a6e1e41", "en", "untitled", [], 0.0, 0, 0, :audio, [], 1, ~U[2023-12-04 06:54:41Z], ~U[2023-12-04 06:54:41Z]]
↳ :erl_eval.do_apply/7, at: erl_eval.erl:746
** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "id" of relation "mediamusic" violates not-null constraint

    table: mediamusic
    column: id

Failing row contains (null, null, d850d2e0-a79a-449b-a5f3-9a317a6e1e41, null, untitled, a/path, en, null, null, 1, {}, null, 0, 0, audio, {}, null, 2023-12-04 06:54:41, 2023-12-04 06:54:41, 0).
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1047: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.10.3) lib/ecto/repo/schema.ex:764: Ecto.Repo.Schema.apply/4
    (ecto 3.10.3) lib/ecto/repo/schema.ex:377: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4

This is the DDL of mediamusic from Postico 2:

-- DDL generated by Postico 2.0.5
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE mediamusic (
    id integer PRIMARY KEY,
    absolute_id integer,
    uuid uuid,
    track_number integer,
    localizedname character varying(255),
    path character varying(255),
    language_id character varying(255),
    presenter_name character varying(255),
    source_material character varying(255),
    music_id integer REFERENCES music(id),
    audio_urls character varying(255)[] NOT NULL DEFAULT ARRAY[]::character varying[],
    poster_path character varying(255),
    duration double precision DEFAULT 0.0,
    size_bytes bigint DEFAULT 0,
    media_format character varying(255) NOT NULL,
    media_urls character varying(255)[] NOT NULL DEFAULT ARRAY[]::character varying[],
    published_at timestamp without time zone,
    inserted_at timestamp(0) without time zone NOT NULL,
    updated_at timestamp(0) without time zone NOT NULL,
    view_count integer DEFAULT 0
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX mediamusic_pkey ON mediamusic(id int4_ops);
CREATE INDEX mediamusic_music_id_index ON mediamusic(music_id int4_ops);
CREATE INDEX mediamusic_uuid_index ON mediamusic(uuid uuid_ops);

integer columns are not auto incremented. You can either use the serial column type, which implicitly generates an autoincrementing sequence and makes the column use its value as default, or you need to mimic the same explicitly for your integer column.

Ecto cannot handle autoincrementing integer columns from runtime/elixir, like it would for uuids.

1 Like

Hey yes, using :serial in the migration fixes the problem of inserting new rows, but only if I do not cast :id

But I need to cast :id if I want to capture the original id values when I import legacy rows with my legacy seed script:

  @doc false
  def changeset(media_music, attrs) do
    media_music
    |> cast(attrs, [
      :id, 

If I don’t cast above, the legacy backreferences to the parent table get mismatched and cannot be inserted.

I seem to be stuck between a rock and a hard place here: if I

  • use :serial in the migration
  • do not cast :id

I can insert new rows but not import legacy rows from legacy seed script

  • use :serial in the migration
  • cast :id

I can insert legacy rows but can’t insert new rows(because when I insert legacy :id, there is an impedance mismatch between the legacy :id and the :serial incrementer)

What’s the error there? If this is changeset related I’d argue that importing legacy data is enough of a different usecase to creating new records, that a separate changeset function is worthwhile.

The error is foreign_key_constraint(scenario 1)

Scenario 1

    use :serial in the migration
    do not cast :id

foreign_key_constraint error insert legacy data:

[priv/repo/seed_1_insert_legacy_data_to_migrated_db.exs:207: (file)]
%MusicTitle{} #=> %Word.Schema.MusicTitle{
  __meta__: #Ecto.Schema.Metadata<:built, "musictitles">,
  id: nil,
  language_id: nil,
  localizedname: nil,
  uuid: nil,
  music_id: nil,
  inserted_at: nil,
  updated_at: nil
}
|> MusicTitle.changeset(attrs) #=> #Ecto.Changeset<
  action: nil,
  changes: %{
    uuid: "37a724f4-cc91-74e6-ca5d-e33b9f9e8d45",
    language_id: "en",
    localizedname: "title",
    music_id: 35
  },
  errors: [],
  data: #Word.Schema.MusicTitle<>,
  valid?: true
>

[debug] QUERY ERROR db=3.1ms idle=10.1ms
INSERT INTO "musictitles" ("uuid","language_id","localizedname","music_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6) RETURNING "id" ["37a724f4-cc91-74e6-ca5d-e33b9f9e8d45", "en", "title", 35, ~N[2023-12-04 14:49:29], ~N[2023-12-04 14:49:29]]
↳ Enum."-map/2-lists^map/1-1-"/2, at: lib/enum.ex:1693
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * "musictitles_music_id_fkey" (foreign_key_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 `foreign_key_constraint/3` on your changeset with the constraint
`:name` as an option.

The changeset has not defined any constraint.

    (ecto 3.10.3) lib/ecto/repo/schema.ex:804: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir 1.15.2) lib/enum.ex:1693: Enum."-map/2-lists^map/1-1-"/2
    (ecto 3.10.3) lib/ecto/repo/schema.ex:788: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto 3.10.3) lib/ecto/repo/schema.ex:769: Ecto.Repo.Schema.apply/4
    (ecto 3.10.3) lib/ecto/repo/schema.ex:377: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (elixir 1.15.2) lib/enum.ex:1693: Enum."-map/2-lists^map/1-1-"/2
    (elixir 1.15.2) lib/enum.ex:1693: Enum."-map/2-lists^map/1-1-"/2
    priv/repo/seed_1_insert_legacy_data_to_migrated_db.exs:192: (file)
    (elixir 1.15.2) lib/code.ex:1435: Code.require_file/2
    (mix 1.15.2) lib/mix/tasks/run.ex:146: Mix.Tasks.Run.run/5
    (mix 1.15.2) lib/mix/tasks/run.ex:85: Mix.Tasks.Run.run/1
    (mix 1.15.2) lib/mix/task.ex:447: anonymous fn/3 in Mix.Task.run_task/5
    (mix 1.15.2) lib/mix/cli.ex:92: Mix.CLI.run_task/2

Scenario 2

    use :serial in the migration
    cast :id

unique_constraint error, insert on new row:

iex(5)> %Word.Schema.MediaMusic{} |> Word.Schema.MediaMusic.changeset(attrs) |> Repo.insert()
[debug] QUERY ERROR db=4.6ms queue=1.1ms idle=756.0ms
INSERT INTO "mediamusic" ("path","uuid","duration","language_id","localizedname","audio_urls","media_format","media_urls","size_bytes","view_count","music_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) RETURNING "id" ["a/path", "185660f4-b15a-4089-99f8-1ba9cd5f2e96", 0.0, "en", "untitled", [], :audio, [], 0, 0, 1, ~U[2023-12-04 14:46:34Z], ~U[2023-12-04 14:46:34Z]]
↳ :erl_eval.do_apply/7, at: erl_eval.erl:746
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * "mediamusic_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.10.3) lib/ecto/repo/schema.ex:804: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir 1.15.2) lib/enum.ex:1693: Enum."-map/2-lists^map/1-1-"/2
    (ecto 3.10.3) lib/ecto/repo/schema.ex:788: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto 3.10.3) lib/ecto/repo/schema.ex:769: Ecto.Repo.Schema.apply/4
    (ecto 3.10.3) lib/ecto/repo/schema.ex:377: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4

That’s likely another unrelated error. A db will abort as soon as it hits the first contraint error, so I’d expect your scenario 2 would suffer from the same issue scenadio 1 does, if it wouldn’t be running into the primary key constraint first.

The issue is there is no row id 35. That is because I’m not casting the legacy id anymore. The error is happening because MusicTitle is looking for a Music row that is serialized in a different order. I wouldn’t have this problem if I just cast the legacy id(but if I do that I can’t insert new rows).

That will always error unless you disable the foreign key constraint. It has nothing to do with what has been discussed before in the thread. You need to insert rows in appropriate order to not run into those errors.

Yes, you’re right. the items in __music.json are not stored in :id ascending order. I’ll try and fix that and hopefully that will solve my problem here.