Ecto: Insert changeset with foreign_key_constraint raise Postgrex.Error

Question

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?

Update:

I'm not using Ecto's migrations. The database tables are already created and migrations are handled by a different project. The SQL Schema look like this:

    Column     |  Type   |                       Modifiers
---------------+---------+-------------------------------------------------------
 id            | integer | not null default nextval('house_id_seq'::regclass)
 owner_id      | integer | not null
Indexes:
    "house_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "house_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES owner(id) DEFERRABLE INITIALLY DEFERRED

Milton Arango G Milton Arango G · Accepted Answer · 2018-01-24T16:05:37

That's weird. I have a really similar scheme and it works as expected with the same constraints.

defmodule Hello.Accounts.Credential do
  use Ecto.Schema
  import Ecto.Changeset
  alias Hello.Accounts.{Credential, User}


  schema "credentials" do
    field :email, :string
    belongs_to :user, User, foreign_key: user_id

    timestamps()
  end

  @doc false
  def changeset(%Credential{} = credential, attrs) do
    credential
    |> cast(attrs, [:email, :user_id])
    |> validate_required([:email, :user_id])
    |> unique_constraint(:email)
    |> foreign_key_constraint(:user_id)
  end
end

When I insert an invalid record (Non existent user_id):

Credential.changeset(%Credential{}, %{email: "[email protected]", user_id: 2}) |> Repo.insert

    {:error,
     #Ecto.Changeset<action: :insert, changes: %{email: "[email protected]", user_id: 2},
      errors: [user_id: {"does not exist", []}], data: #Hello.Accounts.Credential<>,
      valid?: false>}

However, the same error appeared previously but I think it fixed when I reset the database using this task:

mix ecto.drop
mix ecto.create
mix ecto.migrate 
mix run priv/repo/seeds.exs

That task recreates the database and run your migrations.

Credentials SQL Schema:

                                      Table "public.credentials"
   Column    |            Type             |                        Modifiers
-------------+-----------------------------+----------------------------------------------------------
 id          | bigint                      | not null default nextval('credentials_id_seq'::regclass)
 email       | character varying(255)      |
 user_id     | bigint                      | not null
 inserted_at | timestamp without time zone | not null
 updated_at  | timestamp without time zone | not null
Indexes:
    "credentials_pkey" PRIMARY KEY, btree (id)
    "credentials_email_index" UNIQUE, btree (email)
    "credentials_user_id_index" btree (user_id)
Foreign-key constraints:
    "credentials_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Project DB Dependencies:

Phoenix-Ecto -> 3.2 Postgresx >= 0.0.0

Ecto: Insert changeset with foreign_key_constraint raise Postgrex.Error

1 Answers