Phoenix Ecto: foreign key not being inserted

Question

I am inserting a model A that contains a foreign key to another model B.

defmodule MyApp.ModelA do
  use MyApp.Web, :model

  schema "model_a" do
    field :type, :string, null: false
    field :data, :string, null: false
    belongs_to :model_b, MyApp.ModelB
    timestamps()
  end

  @required_fields ~w(type data)
  @optional_fields ~w()

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, @required_fields, @optional_fields)
    |> assoc_constraint(:model_b)
  end
end

and the Model B:

defmodule MyApp.ModelB do
  use MyApp.Web, :model

  schema "model_b" do
    field :username, :string
    field :pass, :string
    has_many :model_a, MyApp.ModelA
    timestamps()
  end

  @required_fields ~w(username pass)
  @optional_fields ~w()

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, @required_fields, @optional_fields)
    |> cast_assoc(:model_a)
    |> validate_required([])
  end
end

Model B exists, as I can get it via Repo.all(ModelB).

Model A changeset is successfully validated, and I can see the model_b_id value when I print model A changeset struct.

But when inserted, the reference is not inserted. Although I can see it when printing the changeset of model A, in the MySQL log, this field is completely missing, is not in the INSERT query.

I have played a little bit, and if I force this reference field to not be null in the MySQL table, then I get a {"does not exist", []} for this foreign key field when inserting as a Repo.insert(...) response, although the model B exists in database.

Changeset validation does not check foreign key validation so you won't get a "does not exist" error before you actually try to insert. Can you post the schema of the models and the code you ran and the SQL logs? — Dogbert
Soryry, maybe I didn't explain myself. I get the "does not exist" as a response of Repo.insert(...). Changeset validation indeed always works ok for this case. — David
Are you absolutely sure you see model_b_id in the changeset? Your @optional_fields is empty according to the code you just posted. Can you try adding model_b_id to @optional_fields? — Dogbert
Yes, absolutely sure I can see it if i print changeset once it has been created and validated. I see the id of the ModelB row in the model_b_id field of the changeset. Mind that this reference field in Model A was added in a migration, so it was not defined in the original table definition, but I don't think it has something to do. I created an extra new field and I was able to set a value to it. So it shouldn't has anything to do to have been defined in a migration. — David
I have tried adding this model_b_id field in required_fields (and also in optional_fields in a second attempt) and I keep getting the same problem, but changeset validation does not complain, so it means that field value is actually set. The problem is when Repo.insert(..). — David

Mac Mac · Accepted Answer · 2016-09-13T13:35:43

I don't think Ecto pulls the ID field off the struct for you - I've been doing something like this:

defmodule MyApp.ModelA do
  use MyApp.Web, :model

  schema "model_a" do
    field :type, :string, null: false
    field :data, :string, null: false
    belongs_to :model_b, MyApp.ModelB
    timestamps()
  end

  @required_fields ~w(type data model_b_id)
  @optional_fields ~w()

  @doc """
  Method head to define what's coming.
  """
  def changeset(model, params \\ :empty)

  @doc """
  Catches the case where you're passing in a model_b struct instead of an integer.
  Just makes a recursive call with the correct type.
  """
  def changeset(model, %{model_b_id: nil, agency: %MyApp.ModelB{} = model_b} = params) do
    changeset(model, %{params | model_b_id: model_b.id})
  end

  @doc """
  I normally use assoc_constraint on the table index itself like so
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, @required_fields, @optional_fields)
    |> assoc_constraint(:model_b, name: :model_as_model_b_id_fkey)
  end
end

Phoenix Ecto: foreign key not being inserted

3 Answers