Confused with nested ecto changeset constraints

Questions / Help
1

I am finding this behavior confusing. If I do:

iex(135)> PenguinMemories.Repo.insert(c)                                                                                                 
[debug] QUERY ERROR db=0.5ms queue=0.3ms idle=117.8ms
INSERT INTO "spud_photo_album" ("album_id","photo_id") VALUES ($1,$2) RETURNING "id" [232323, 123123123]
[error] Postgrex.Protocol (#PID<0.436.0>) disconnected: ** (Postgrex.Error) ERROR 23503 (foreign_key_violation) insert or update on table "spud_photo_album" violates foreign key constraint "album_id_refs_album_id_58ff3a98"

    table: spud_photo_album
    constraint: album_id_refs_album_id_58ff3a98

Key (album_id)=(232323) is not present in table "spud_album".
{:error,
 #Ecto.Changeset<
   action: :insert,
   changes: %{album_id: 232323, photo_id: 123123123},
   errors: [
     album_id: {"does not exist",
      [constraint: :foreign, constraint_name: "album_id_refs_album_id_58ff3a98"]}
   ],
   data: #PenguinMemories.Photos.PhotoAlbum<>,
   valid?: false
 >}

OK, good, the foreign key constraint is working exactly like I expect. I get the error that I am expecting. photo_id is also invalid, but I assume I can only get one error, fair enough. Good.

Then if I do:

iex(138)> o.photo_albums
[
  %PenguinMemories.Photos.PhotoAlbum{
    __meta__: #Ecto.Schema.Metadata<:loaded, "spud_photo_album">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    id: 128139,
    photo: #Ecto.Association.NotLoaded<association :photo is not loaded>,
    photo_id: 1
  }
]
iex(136)> Ecto.Changeset.cast(o, %{}, []) |> Ecto.Changeset.put_assoc(:photo_albums, [c])                                 
#Ecto.Changeset<
  action: nil,
  changes: %{
    photo_albums: [
      #Ecto.Changeset<action: :replace, changes: %{}, errors: [],
       data: #PenguinMemories.Photos.PhotoAlbum<>, valid?: true>,
      #Ecto.Changeset<
        action: :insert,
        changes: %{album_id: 232323, photo_id: 123123123},
        errors: [],
        data: #PenguinMemories.Photos.PhotoAlbum<>,
        valid?: true
      >
    ]
  },
  errors: [],
  data: #PenguinMemories.Photos.Photo<>,
  valid?: true
>

OK, fine, I am no expert, but everything looks fine here. But then if I try to update this changeset:

iex(139)> Ecto.Changeset.cast(o, %{}, []) |> Ecto.Changeset.put_assoc(:photo_albums, [c]) |> PenguinMemories.Repo.update()
[debug] QUERY OK db=0.8ms idle=1226.4ms
begin []
[debug] QUERY OK db=1.7ms
DELETE FROM "spud_photo_album" WHERE "id" = $1 [128139]
[debug] QUERY OK db=0.2ms
INSERT INTO "spud_photo_album" ("album_id","photo_id") VALUES ($1,$2) RETURNING "id" [232323, 1]
** (Postgrex.Error) ERROR 23503 (foreign_key_violation) insert or update on table "spud_photo_album" violates foreign key constraint "album_id_refs_album_id_58ff3a98"

    table: spud_photo_album
    constraint: album_id_refs_album_id_58ff3a98

Key (album_id)=(232323) is not present in table "spud_album".
    (db_connection 2.2.2) lib/db_connection.ex:1455: DBConnection.run_transaction/4
[debug] QUERY ERROR db=0.4ms
commit []
[error] Postgrex.Protocol (#PID<0.435.0>) disconnected: ** (Postgrex.Error) ERROR 23503 (foreign_key_violation) insert or update on table "spud_photo_album" violates foreign key constraint "album_id_refs_album_id_58ff3a98"

    table: spud_photo_album
    constraint: album_id_refs_album_id_58ff3a98

Key (album_id)=(232323) is not present in table "spud_album".

OK, so it is:

  • Deleting the existing item, which is as expected.
  • Trying to insert new entry with photo_id=1. This confused me for a while until I remembered o.id=1. Fine. That is good. I guess photo_id is optional, but my changeset function requires it.
  • Generating an exception instead of a changeset error, which I was expecting.

All I can think of it maybe in this situation maybe I should be using a multi instead of a changeset. I probably should think about how to structure my code to do this. But I thought the above should work too (???). If my expectations are wrong, would appreciate pointers to the documentation that highlight this. Maybe it is the case that nested constraints do not work.

Thanks

2

I probably should make my goal clear. I have a comma separated list of album ids, and when I call the changeset function:

changeset(photo, %{"album_ids" => "1,2,3"})

Ideally I would like it to generate changeset that completely replaces the set of albums (a “simple” many to many relationship) with the values specified. So I currently a function that automatically splits the string list and converts it into the data as above. But as the data is untrusted, I want it to cope appropriately with errors. If there is an easier/better way to do this, suggestions appreciated also.

3

The behavior when a constraint violation error is returned is defined by how the changeset is built - can you show the code that’s producing c above?

4

As per example above, I know this works, but anyway:

def changeset(photo_album, attrs) do
    photo_album
    |> cast(attrs, [:photo_id, :album_id])
    |> validate_required([:photo_id, :album_id])
    |> foreign_key_constraint(:photo_id, name: :photo_id_refs_photo_id_56180e95)
    |> foreign_key_constraint(:album_id, name: :album_id_refs_album_id_58ff3a98)
  end
5

Reading the log again, here’s what I’m seeing:

  • deletes old record

  • tries to insert new record

  • gets a constraint error, maps it to a changeset error and continues

  • tries to commit the transaction (the commit line)

  • gets a foreign-key constraint error from the transaction (the second error message) which bubbles out

I’d have expected the first changeset error to trigger a rollback, so that’s odd…

Can you try updating any column on o as well? I’m wondering if Ecto.Changeset.cast(o, %{}, []) is triggering some “we don’t have any updates so just commit” logic.

6

No joy,

iex(6)> Ecto.Changeset.cast(o, %{title: "hello2"}, [:title]) |> Ecto.Changeset.put_assoc(:photo_albums, [c]) |> PenguinMemories.Repo.update()
[debug] QUERY OK db=0.7ms idle=1834.5ms
begin []
[debug] QUERY OK db=1.7ms
UPDATE "spud_photo" SET "title" = $1 WHERE "id" = $2 ["hello2", 1]
[debug] QUERY OK db=0.2ms
DELETE FROM "spud_photo_album" WHERE "id" = $1 [128139]
[debug] QUERY OK db=0.1ms
INSERT INTO "spud_photo_album" ("album_id","photo_id") VALUES ($1,$2) RETURNING "id" [232323, 1]
[debug] QUERY ERROR db=0.3ms
commit []
[error] Postgrex.Protocol (#PID<0.401.0>) disconnected: ** (Postgrex.Error) ERROR 23503 (foreign_key_violation) insert or update on table "spud_photo_album" violates foreign key constraint "album_id_refs_album_id_58ff3a98"

    table: spud_photo_album
    constraint: album_id_refs_album_id_58ff3a98

Key (album_id)=(232323) is not present in table "spud_album".
** (Postgrex.Error) ERROR 23503 (foreign_key_violation) insert or update on table "spud_photo_album" violates foreign key constraint "album_id_refs_album_id_58ff3a98"

    table: spud_photo_album
    constraint: album_id_refs_album_id_58ff3a98

Key (album_id)=(232323) is not present in table "spud_album".
    (db_connection 2.2.2) lib/db_connection.ex:1455: DBConnection.run_transaction/4