Non-id foreign keys in Ecto

Questions / Help
1

Model " Category " has only id and name.

Now I want to create Article and refer to Category as a foreign key, but not by “id”, but by “name”.

This doesn’t work:

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

  def change do
    create table(:articles) do
      add :category, references(:categories, column: :name)

Error:

** (Postgrex.Error) ERROR 42804 (datatype_mismatch) foreign key constraint "articles s_category_fkey" cannot be implemented

Key columns "category" and "name" are of incompatible types: bigint and character varying.

How to fix this?

Also, what will that foreign key look like in the model Article? “belongs_to :category, ???” …

2

:wave:

Try adding type: :string to the list of options.

add :category, references(:categories, column: :name, type: :string)

Also, what will that foreign key look like in the model Article? “belongs_to :category, ???” …

belongs_to :category, Category,
  references: :name,
  foreign_key: :category_name,
  type: :string
5 Likes
3

is this required?

4

I’d think so, otherwise the schema would try to use :category_id for the foreign key, and according to the migration we have :category.

5

The belongs_to would actually be

belongs_to :category, Category,
  references: :name,
  foreign_key: :category, # <-- since in the migration we have `add :category` and not `add :category_name`, I misread at first ...
  type: :string
1 Like