Ecto.NoPrimaryKeyValueError: ecto join and many-to-many

I’ve been trying to connect 2 tables (for over a week) using a join table as described in Ecto Association Guide – Ecto v2.2.11. Best docs I could find.

I have three tables: organizations, employees, organization_employees. There is no schema for organization_employees

The latest issue is a PK error. I insert and preload the “objects”, but when I try to update the DB with put_assoc I get an error. The “objects” do exist in the DB and PK matches.

A breif snipped is below while the full code is here: Loading put_assoc · GitHub

org_update = Ecto.Changeset.put_assoc(org_changeset, :employees, [e_insert])
  action: nil,
  changes: %{
    email: "",
    employees: [
      #Ecto.Changeset<action: :update, changes: %{}, errors: [],
       data: #Employee<>, valid?: true>
    name: "Toys R Us",
    phone: "777777777",
    slug: "toys-r-us"
  errors: [],
  data: .Organization<>,
  valid?: true

** (Ecto.NoPrimaryKeyValueError) struct `%TurnStile.Company.Organization{__meta__: #Ecto.Schema.Metadata<:built, "organizations">, id: nil, email: nil, name: nil, slug: nil, phone: nil, employees: #Ecto.Association.NotLoaded<association :employees is not loaded>, inserted_at: nil, updated_at: nil}` is missing primary key value
    (ecto 3.9.4) lib/ecto/repo/schema.ex:977: anonymous fn/3 in Ecto.Repo.Schema.add_pk_filter!/2
    (elixir 1.14.1) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.9.4) lib/ecto/repo/schema.ex:416: Ecto.Repo.Schema.do_update/4
    iex:13: (file)

You can see in the full code that I do preload, so I don’t know why it says employees is not loaded. Even after preloading, employees is an empy list [], even though there is an employee in the DB. Vice versa for organizations. Not sure if this is how it’s supposed to be.

This has been SO error prone and tedious. Any help is appreciated.

According to the error, this specific issue is not related to the join, but with the organization struct itself. Ecto can’t update it because it does not have a primary key. How was it instantiated? Usually such a struct would be loaded via a query generated by Ecto (e.g. get/1), and that field would be automatically filled.

Thanks for the tip. You mean this?

# org params
iex> org = %{
    "email" => "",
    "name" => "Toys R Us",
    "phone" => "777777777",
    "slug" => "toys-r-us"
# make org changeset
iex> org_changeset = Organization.changeset(%Organization{},  org)
# #Ecto.Changeset<
#   action: nil,
#   changes: %{
#     email: "",
#     name: "Toys R Us",
#     phone: "777777777",
#     slug: "toys-r-us"
#   },
#   errors: [],
#   data: #Organization<>,
#   valid?: true
# >
# insert org and preload 
iex> org_insert = Repo.insert!(org_changeset) |> Repo.preload(:employees)
# %Organization{
#   __meta__: #Ecto.Schema.Metadata<:loaded, "organizations">,
#   id: 108,
#   email: "",
#   name: "Toys R Us",
#   slug: "toys-r-us",
#   phone: "777777777",
#   employees: [],
#   inserted_at: ~N[2023-05-23 17:42:04],
#   updated_at: ~N[2023-05-23 17:42:04]
# }

The id is auto generated. It’s not on the schema

schema "organizations" do
    field :email, :string
    field :name, :string
    field :slug, :string
    field :phone, :string
    many_to_many :employees, Employee, join_through: "organization_employees"


select * from organizations;
 id |   name    |   slug    |    email     |   phone   |     inserted_at     |     updated_at      
  108 | Toys R Us | toys-r-us | | 777777777 | 2023-05-23 17:42:04 | 2023-05-23 17:42:04

I am not doing anything to specifically handle the PK since I can’t find any instructions telling me how. Should I be?


Sorry, I was having a hard time following your code, you need to build a changeset with the org_insert struct. It is hard to be more specific without seeing even more code though, since this is not an idiomatic way to insert a parent and children. Probably you want cast_assoc on the original changeset instead of two separate inserts, or possibly a multi.

Docs for cast_assoc might help: Ecto.Changeset — Ecto v3.10.1

1 Like

You’re passing the unsaved Organization changeset (org_changeset) to put_assoc later - the ID from the database is in the org_insert struct, not in that changeset.

I tried something like this before posting here, adding the ID manually. Still I had the PK error. No idea why

Making sure the ID is present DOES make the error change to another one, so that’s good!
null value in column "inserted_at" violates not-null constraint
So adding the ID did something, this time.

# build changeset
iex> org_changeset = Organization.changeset(%Organization{},  org)
# insert it
iex> org_insert = Repo.insert!(org_changeset) |> Repo.preload(:employees)
 # add the id manually
iex> org_update = Ecto.Changeset.put_change(org_changeset, :id,
iex> org_put = Ecto.Changeset.put_assoc(org_update, :employees, [emp_insert])    
# update DB
iex> Repo.update(org_put)

I don’t see what causes this error as the table has a timestamps field, but this is new error at least. There is no schema handling this though. That’s prob why.

def change do
    create table(:organization_employees) do
      add :organization_id, references(:organizations)
      add :employee_id, references(:employees)


The code is at that link I posted above I thought it be too daunting to put it all here Or U mean more than that?

This is just a simplified iex version to try and make something, anything at all, work.

The only technical problem in your code causing the error you are getting is that you are passing an incorrect changeset to put_assoc. The original changeset used for the insert will not have the id because that is loaded only after the insert. This is the relevant example code from the guide you are following:

post_changeset = Ecto.Changeset.change(post)

post_with_tags = Ecto.Changeset.put_assoc(post_changeset, :tags, [clickbait_tag, misc_tag])

post = Repo.update!(post_with_tags)

Notice that the new changeset for the update is created using change on the result of the parent insert, and then that is passed to put_assoc

1 Like