Relation has_many with custom primary key

Hello, I am trying to create a relation has_may with a custom primary_key :

My schemes are the following

defmodule Example.Accounts.User do
  use Ecto.Schema
  import Ecto.Changeset
  alias Example.Accounts.User

  @primary_key{:identification, :id, []}
  @derive {Phoenix.Param, key: :identification}
  schema "users" do
    field :name, :string
    has_many :properties, Example.Properties.Property
    timestamps()
  end

  @doc false
  def changeset(%User{} = user, attrs) do
    user
    |> cast(attrs, [:identification, :name])
    |> validate_required([:identification, :name])
  end
end

And

defmodule Example.Properties.Property do
  use Ecto.Schema
  import Ecto.Changeset
  alias Example.Properties.Property


  schema "properties" do
    field :n_property, :integer
    field :proprietor, :id
    belongs_to :user, Example.Accounts.User

    timestamps()
  end

  @doc false
  def changeset(%Property{} = property, attrs) do
    property
    |> cast(attrs, [:n_property])
    |> validate_required([:n_property])
  end
end

My migrations files are the following:

defmodule Example.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users, primary_key: false) do
      add :identification, :id, primary_key: true
      add :name, :string

      timestamps()
    end

  end
end

And

defmodule Example.Repo.Migrations.CreateProperties do
  use Ecto.Migration

  def change do
    create table(:properties) do
      add :n_property, :integer
      add :proprietor, references("users")

      timestamps()
    end

  end
end

But when I run mix ecto.migrate, I get the following error

(Postgrex.Error) ERROR 42703 (undefined_column): There is no column "id" referenced in the foreign key

The id column is the default primary key for the table user but in this case should be “identification” according to the configuration of the schema…

If you’re using a primary key other than :id, you need to specify it in the schema with the :references and :foreign_key option.

Take a look at the options for Ecto.Schema.has_many/3:
https://hexdocs.pm/ecto/Ecto.Schema.html#has_many/3-options

Thanks for you answer :smiley:

I followed your indications and modified my scheme

  schema "properties" do
    field :n_property, :integer
    field :proprietor, :id
    belongs_to :user, Example.Accounts.User, references: :identification, foreign_key: :proprietor

    timestamps()
  end

But now when I run ecto.migrate get the following error:

field/association :proprietor is already set on schema

Unlike certain ORMs, Ecto strictly distinguishes between a foreign key and an association. While the foreign key is just a column with a value, associations are placeholders for other structs to be loaded.
This means you cannot use the same name for the foreign key and the association if you want to have access to both from your schema.

The easy way around is naming your foreign keys something like user_id insted of just user.
Alternatively, if you don’t need direct access to the foreign key, you could simply omit it from your schema.

By the way: What you define in a schema is completely independent from your migrations. So the reason you’re getting these errors while running migrations is really that this makes your whole application compile :slight_smile:

3 Likes