Ecto: Insert changeset with foreign_key_constraint raise Postgrex.Error

Questions / Help
,
1

I’m using Ecto (2.2.8) to work with an existent PostgreSQL database.

I defined two schemas to represent owner and house. The house schema has a FK (belongs_to) owner. I defined a schema and a changeset for house that look as follows:

 @primary_key {:id, :id, autogenerate: true}
  schema "house" do
    belongs_to :owner, Owner, foreign_key: :owner_id
    field :name, :string
  end

  def changeset(house, params \\ %{}) do
    house
    |> cast(params, [:name, :owner_id])
    |> validate_required([:owner_id])
    |> foreign_key_constraint(:owner_id)
  end

The Problem

I expected the following code to return a tuple {:error, changeset}, when there isn’t a owner record with id 10 in database:

House.changeset(%House{}, %{name: "Whatever",  owner_id: 10}) |> Repo.insert

However, I’m getting a Postgrex.Error:

** (Postgrex.Error) ERROR 23503 (foreign_key_violation): insert or update on table "house" violates foreign key constraint "house_owner_id_fkey".

If I check the content of changeset.constraints before calling Repo.insert, this is what I get:

House.changeset(%House{}, %{name: "Whatever",  owner_id: 10}) 
|> Map.get(:constraints)

[
  %{
    constraint: "house_owner_id_fkey",
    error: {"does not exist", []},
    field: :owner_id,
    match: :exact,
    type: :foreign_key
  }
]

So, when using foreign_key_constraint shouldn’t I get a tuple {:error, changeset} that I can pattern match as the docs suggests?

This question was originally posted on StackOverflow, but someone at Slack suggested me to post it here: https://stackoverflow.com/questions/48425959/ecto-insert-changeset-with-foreign-key-constraint-raise-postgrex-error

2

Your code seems correct. You mentioned that the database was created from outside of Ecto so one possible issue could have been a different naming scheme for the constraint.

From the additional information you posted on S.O. and from the error message, however, it appears that the constraint name ("house_owner_id_fkey") matches the one generated automatically by Ecto. If this weren’t the case, you could supply a custom constraint name with the :name option.

You might want to file a bug report in Ecto’s GitHub repository, preferably with a way to reproduce the issue (e. g. with a minimal database dump).

3

Thanks for your reply @wmnnd.

I created an issue in Ecto’s GitHub repository. It seems like Ecto doesn’t support DEFERRED Foreign Key constraints:

1 Like