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

maz · December 4, 2023, 5:14am

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:

gist.github.com

https://gist.github.com/mazz/50c8b51421d3e784f3d164dc3bd7963c

20181013204438_create_music.exs

defmodule Word.Repo.Migrations.CreateMusic do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION IF NOT EXISTS citext", ""

    create table(:music, primary_key: false) do
      add :id, :id, primary_key: true
      add :absolute_id, :integer
      add :uuid, :uuid

This file has been truncated. show original

20181013204617_create_mediamusic.exs

defmodule FaithfulWord.Repo.Migrations.CreateMediamusic do
  use Ecto.Migration

  def change do
    create table(:mediamusic, primary_key: false) do
      add :id, :id, primary_key: true
      add :absolute_id, :integer
      add :uuid, :uuid
      add :track_number, :integer
      add :localizedname, :string

This file has been truncated. show original

media_music.ex

defmodule Word.Schema.MediaMusic do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :id, autogenerate: true}

  schema "mediamusic" do
    field :absolute_id, :integer
    field :language_id, :string
    field :localizedname, :string

This file has been truncated. show original

There are more than three files. show original

Thanks,
Michael

LostKobrakai · December 4, 2023, 6:06am

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

maz · December 4, 2023, 6:59am

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);

LostKobrakai · December 4, 2023, 9:00am

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.

maz · December 4, 2023, 1:52pm

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)

LostKobrakai · December 4, 2023, 1:59pm

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.

maz · December 4, 2023, 2:52pm

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

LostKobrakai · December 4, 2023, 2:54pm

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.

maz · December 4, 2023, 3:00pm

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).

LostKobrakai · December 4, 2023, 3:04pm

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.

maz · December 4, 2023, 3:06pm

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.