Many_to_many association with composite primary keys : ERROR 42830 (invalid_foreign_key)

Hi there. I’ve been trying for the past two days to fix this error I’ve been having with relationships from a table that has two primary keys. The main error I keep seeing is ERROR 42830 (invalid_foreign_key) there is no unique constraint matching given keys for referenced table "albums", on the add :album_id, references(:albums, name: :albums_id_is_single) line in my CreateAlbumTags migration.

At this point, after consulting the ecto docs and multiple StackOverflow posts on this problem, I come with my own plea. Any input would be greatly appreciated. Thanks in advance!


(I wasn’t sure what all would be necessary, so I’ve included the majority of my qualm’s content.)

First, a note about the album primary key:

  • :id, a Uint64 :binary
  • :is_single, a :boolean that determines if the album is a single or a regular album.

If we separate the single albums from regular albums, the ids are unique. But if I have the migration as:

create unique_index(:albums, [:id])
create unique_index(:albums, [:is_single])

Then I can’t insert more than one of each album type into the DB.

Schema

defmodule MyApp.Album do
  @primary_key false
  schema "albums" do
    field :id, MyApp.Uint64, primary_key: true
    field :is_single, :boolean, primary_key: true
    # ...
    has_many :listenables, MyApp.Listenable, references: :id
    many_to_many :tags, MyApp.Tag, join_through: "albums_tags"
  end

  def changeset(album, attrs \\ %{}) do
    album
    |> cast(attrs, @fields)
    |> cast_assoc(:listenables, with: &MyApp.Listenable.changeset/2)
    |> cast_assoc(:tags, with: &MyApp.Tag.changeset/2)
    |> validate_required(@required)
    |> unique_constraint(:id_is_single, name: :albums_id_is_single)
  end
end

defmodule MyApp.Tag do
  schema "tags" do
    field :name, :string
    many_to_many :albums, MyApp.Album, join_through: "albums_tags"
  end

  def changeset(tag, attrs \\ %{}) do
    tag
    |> cast(attrs, @fields)
    |> validate_required(@required)
    |> unique_constraint(:name)
  end
end

defmodule MyApp.Listenable do
  @primary_key {:id, MyApp.Uint64, autogenerate: false}
  schema "listenables" do
    # ...
    belongs_to :album, MyApp.Album, type: MyApp.Uint64
  end

  def changeset(to_do, attrs \\ %{}) do
    to_do
    |> cast(attrs, @fields)
    |> validate_required(@required)
    |> foreign_key_constraint(:album_id)
    |> unique_constraint(:album_id, name: :listenables_album_id_index)
  end
end

Migrations

defmodule MyApp.Repo.Migrations.CreateAlbum do
  use Ecto.Migration

  def change do
    create table(:albums, primary_key: false) do
      add :id, :binary, primary_key: true
      add :is_single, :boolean, primary_key: true
      # ...
    end

    create unique_index(:albums, [:id, :is_single], name: :albums_id_is_single)
  end
end

defmodule MyApp.Repo.Migrations.CreateTag do
  use Ecto.Migration

  def change do
    create table(:tags) do
      add :name, :string
    end

    create unique_index(:tags, [:name])
  end
end

defmodule MyApp.Repo.Migrations.CreateAlbumTags do
  use Ecto.Migration

  def change do
    create table("album_tags", primary_key: false) do
      add :album_id, references(:albums, name: :albums_id_is_single)
      add :tag_id, references(:tags)
    end
  end
end

defmodule MyApp.Repo.Migrations.CreateListenable do
  use Ecto.Migration

  def change do
    create_if_not_exists table(:listenables, primary_key: false) do
      add :id, :binary, primary_key: true
      # ...
      add :album_id, references(:albums, type: :binary, on_delete: :delete_all), null: false
    end

    # create_if_not_exists(index(:listenables, :album_id, name: :listenables_album_id))
    # create_if_not_exists(unique_index(:listenables, [:album_id]))
  end
end

Hi,

Your problem might be that the associations doesn’t identify the assoc id. You can try to follow this answer https://stackoverflow.com/questions/43158835/foreign-key-name-for-many-to-many-association-in-ecto

1 Like

The name argument here gets used in the resulting DDL as the name of the foreign key constraint added to album_id, AFAIK it’s not relevant here.

The bigger issue is In the implementation of references in Ecto SQL, the code assumes there’s exactly one column involved in the reference:

(line 1045 with ref.column in particular)

References to album’s primary key will need to have two columns (album_id and album_is_single) so you’ll need to write those out manually.

But backing up for a second: are you certain you want to go down this route? There’s going to be a LOT of added complexity with composite primary keys - for instance, associations don’t work.