Elixir -- Ecto -- Composite Primary Keys and Relationships

Question

Has anyone successfully created relationships to/from tables with composite primary keys? I'm trying to achieve the following:

create table(:resources, primary_key: false) do
  add :id, :uuid, primary_key: true
  add :version, :id, primary_key: true 
  # ... other details about the resource
end

create table (:resource_instances, primary_key: false) do
  add :id, :uuid, primary_key: true
  add :resource_id,
      references(:resources, type: :uuid, column: :id),
      null: false

  add :resource_version,
      references(:resources, type: :id, column: :version),
      null: false
  # ... specific details about this instance of the resource
end

Running the migration produces this log:

11:24:00.100 [info]  create table my_schema.resources

11:24:00.152 [info]  create table my_schema.resource_instances
** (Postgrex.Error) ERROR 42830 (invalid_foreign_key) there is no unique constraint matching given keys for referenced table "resources"
    (ecto_sql) lib/ecto/adapters/sql.ex:629: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql) lib/ecto/adapters/sql.ex:716: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:166: :timer.tc/1
    (ecto_sql) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/7
    (ecto_sql) lib/ecto/migrator.ex:342: Ecto.Migrator.attempt/7
    (ecto_sql) lib/ecto/migrator.ex:243: anonymous fn/4 in Ecto.Migrator.do_up/4
    (ecto_sql) lib/ecto/migrator.ex:324: anonymous fn/3 in Ecto.Migrator.run_maybe_in_transaction/6
    (ecto_sql) lib/ecto/adapters/sql.ex:898: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection) lib/db_connection.ex:1415: DBConnection.run_transaction/4
    (ecto_sql) lib/ecto/migrator.ex:323: Ecto.Migrator.run_maybe_in_transaction/6
    (elixir) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (elixir) lib/task/supervised.ex:35: Task.Supervised.reply/5
    (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3

user2432854 · Accepted Answer · 2019-12-19T15:12:47.727

1

try this:

    create table(:resources, primary_key: false) do
        add :id, :uuid, primary_key: true
        add :version, :id, primary_key: true, auto_generate: true
    end
    create unique_index(:resources, [:id])
    create unique_index(:resources, [:version])

    create table(:resource_instances, primary_key: false) do
        add :resource_id,
            references(:resources, type: :uuid, column: :id),
            null: false
        add :resource_version,
            references(:resources, type: :id, column: :version),
            null: false
    end

it seems like since you are creating a composite key you also need to create the separate unique indices to be able to reference those so basically added the unique_indices for the individual columns

edited Dec 19 '19 at 15:12

answered Dec 18 '19 at 19:20

user2432854

26
2

1

this answer would be better if it included an explanation of what you changed and why – Kaan Dec 18 '19 at 19:31
Thanks! This solution works. I believe the explanation is you must create unique indices on the table because Ecto doesn't do that automatically. We're referencing one of the PK in each of the `references` in the second table. Not sure what a `connector` or a `connector_profile` is, but this message got the point across – Kyle V Dec 19 '19 at 14:58

Elixir -- Ecto -- Composite Primary Keys and Relationships

1 Answers1