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
, aUint64
: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