0

I have two tables, that looks like: 

def change do

    create table(:companies_classification) do

      add :code, :integer
      add :iso_language, references(:languages_code, column: :iso, type: :string)
      add :name, :string

      timestamps

    end

    create unique_index(:companies_classification, [:code, :iso_language])

  end

and the second table:

create table(:companies) do

      add :name,           :string
      add :classification, references(:companies_classification, column: :code, type: :integer)
      add :country,        references(:countries_code, column: :iso, type: :string)
      add :email,          :string
      add :password_hash,  :string

      timestamps

    end

By migration, I've got error:

14:28:07.206 [info]  create table companies
** (Postgrex.Error) ERROR (invalid_foreign_key): there is no unique constraint matching given keys for referenced table "companies_classification"
    (ecto) lib/ecto/adapters/sql.ex:187: Ecto.Adapters.SQL.query!/5
    (ecto) lib/ecto/adapters/postgres.ex:71: Ecto.Adapters.Postgres.execute_ddl/3
    (ecto) lib/ecto/migration/runner.ex:101: anonymous fn/2 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1623: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/migration/runner.ex:99: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:181: :timer.tc/2
    (ecto) lib/ecto/migration/runner.ex:27: Ecto.Migration.Runner.run/6
    (ecto) lib/ecto/migrator.ex:121: Ecto.Migrator.attempt/6
    (ecto) lib/ecto/migrator.ex:71: anonymous fn/4 in Ecto.Migrator.do_up/4
    (ecto) lib/ecto/adapters/sql.ex:508: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
    (db_connection) lib/db_connection.ex:1274: DBConnection.transaction_run/4
    (db_connection) lib/db_connection.ex:1198: DBConnection.run_begin/3
    (db_connection) lib/db_connection.ex:789: DBConnection.transaction/3
    (ecto) lib/ecto/migrator.ex:244: anonymous fn/4 in Ecto.Migrator.migrate/4
    (elixir) lib/enum.ex:1184: Enum."-map/2-lists^map/1-0-"/2
    (elixir) lib/enum.ex:1184: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/mix/tasks/ecto.migrate.ex:84: anonymous fn/4 in Mix.Tasks.Ecto.Migrate.run/2
    (elixir) lib/enum.ex:651: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:651: Enum.each/2
    (mix) lib/mix/task.ex:296: Mix.Task.run_task/3

the error appears, because the unique index of companies_classification?
If yes, how to check if the value contains on the companies_classification?

softshipper
  • 32,463
  • 51
  • 192
  • 400
  • 1
    Possible duplicate of [What is causing ERROR: there is no unique constraint matching given keys for referenced table?](http://stackoverflow.com/questions/11966420/what-is-causing-error-there-is-no-unique-constraint-matching-given-keys-for-ref) – Dogbert Dec 01 '16 at 13:45
  • 2
    http://stackoverflow.com/questions/11966420/what-is-causing-error-there-is-no-unique-constraint-matching-given-keys-for-ref You need to add unique index for `:code` alone as well if you want to use it as a foreign key in another table. – Dogbert Dec 01 '16 at 13:45
  • Aha ok. I will do it thanks. – softshipper Dec 01 '16 at 13:47
  • Out of curiosity, why do you need to use `code` and `iso` as foreign keys? What's stopping you from pointing to the `id` primary key instead? – Topher Hunt Dec 02 '16 at 16:27
  • Because `id` is not clearly for me. And in one day, if I would change the database, it would not be better to use `code` as foreign key, because the id could change or `id` should be enough. I am coming from SAP and in SAP there is no autogenerated id. – softshipper Dec 03 '16 at 09:05

0 Answers0