Build has_one relationship

Question

I have schemas, that looks as follow:

defmodule Busiket.LanguageCode do

  use Busiket.Web, :model


  schema "languages_code" do

        field :code, :string
        field :text, :string

        timestamps
  end
end

the second schema:

defmodule Busiket.CountryCode do

  use Busiket.Web, :model

  schema "countries_code" do

    field :alpha2, :string
    field :alpha3, :string

    timestamps
  end

end

and the third table

defmodule Busiket.Country do

  use Busiket.Web, :model

  alias Busiket.LanguageCode
  alias Busiket.CountryCode

  schema "countries" do

    has_one :code, CountryCode
    has_one :lang, LanguageCode
    field :text, :string

    timestamps

  end

end

as you can see on the third schema, the field code should depends on country_code schema with field code.

The lang field should depends on language_code schema with field alpha2.

I do not know, if the schema country is well designed?

The entries in the countries should looks like:

"CH" | "EN" | "Switzerland"
"DE" | "EN" | "Germany"

and this record should faild:

"YY" | "EN" | "Foo"

because there is no a country with YY iso code.

The migration file from language_code looks as follow:

defmodule Busiket.Repo.Migrations.CreateLanguageCode do
  use Ecto.Migration

  def change do

     create table(:languages_code) do

      add :code, :string, size: 3
      add :text, :string

      timestamps

    end

  end
end

and country_code

defmodule Busiket.Repo.Migrations.CreateCountryCode do
  use Ecto.Migration

  def change do

      create table(:countries_code) do

      add :alpha2, :string, size: 2
      add :alpha3, :string, size: 3

      timestamps
    end

  end
end

and at last I tried with country migration:

defmodule Busiket.Repo.Migrations.CreateCountryTable do
  use Ecto.Migration

  def change do

    create table(:countries) do

    add :code, references(:countries_code), [name: :alpha2]
    add :lang, references(:languages_code), [name: :code]
    add :text, :string

    timestamps

    create unique_index(:countries, [:code, :lang])

    end
  end
end

I hope, it is clear what I want to reach.

UPDATE

I created the table as you sad:

defmodule Busiket.Repo.Migrations.CreateCountryTable do
  use Ecto.Migration

  def change do

        create table(:countries) do

          add :coun, references(:countries_code, column: :alpha2, type: :string)
      add :lang, references(:languages_code, column: :code, type: :string)
          add :text, :string

          timestamps
        end

        create unique_index(:countries, [:coun, :lang])

  end
end

When I execute mix ecto.migrate, I've got following error:

20:34:11.012 [info]  create table countries
** (Postgrex.Error) ERROR (invalid_foreign_key): there is no unique constraint matching given keys for referenced table "countries_code"

I think, I have to change :alpha3 not to be unique.

Answer 1

Two things:

1. You want belongs_to in Country since Country's table contains the foreign keys. You also need to specify the foreign table's column name in belongs_to.

   schema "countries" do
     belongs_to :code, CountryCode, foreign_key: :alpha2
     belongs_to :lang, LanguageCode, foreign_key: :code
     ...
   end
   

   The has_one declarations should be in CountryCode and LanguageCode schemas.

2. The option you want to specify in the migration is column, and that should be in the call to references. (There is no name option in add, which your current code is using.) You also need to specify type.

   create table(:countries) do
     add :code, references(:countries_code, column: :alpha2, type: :string)
     add :lang, references(:languages_code, column: :code, type: :string)
     ...
   end