composite primary keys in ecto

Question

In the migration we can add multiple primary keys they all act as composite primary keys. I have four fields in the table three of them are primary keys and works perfectly . When I try to add a new migration and make the fourth column my primary key. Postgres gives me this error.

   (Postgrex.Error) ERROR 42P16 (invalid_table_definition) multiple primary keys for table "rooms_units" are not allowed

This is my new migration:

alter table(:rooms_units) do
  modify(:date_to, :utc_datetime, primary_key: true)  
end 

This works fine if I add this in the original migration.

This is my original migration:

  create table(:rooms_units, primary_key: false) do
   add(:room_id, references(:rooms), null: false, primary_key: true)
   add(:unit_id, references(:units), null: false, primary_key: true)
   add(:date_from, :utc_datetime, null: false, primary_key: true)
   add(:date_to, :utc_datetime, null: false, default: fragment("'infinity'::timestamp"))
  end

This works fine if i added the primary_key: true in the date_to column. Migration runs successfully.

so I guess syntax is right. But it won’t work in a new migration.

Any suggestions or workaround ? Thanks.

Answer 1

You have a primary key which happens to be a composite key.

dbname=# \d+ rooms_units
   ...

Indexes:
"rooms_units_pkey" PRIMARY KEY, btree (room_id, unit_id, date_from)

   ...

It's not possible to add another primary key. Each table can only have one primary key (that's the definition of pk). But you can drop the current primary key and create a new one. There're probably more elegant ways to do this in Ecto but this works

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

  def change do
    drop(constraint("rooms_units", "rooms_units_pkey"))

    alter table(:rooms_units) do
      modify(:room_id, :integer, primary_key: true)
      modify(:unit_id, :integer, primary_key: true)
      modify(:date_from, :utc_datetime, primary_key: true)
      modify(:date_to, :utc_datetime, primary_key: true)
    end
  end
end

Now we have a new composite primary key

dbname=# \d+ rooms_units
   ...

Indexes:
"rooms_units_pkey" PRIMARY KEY, btree (room_id, unit_id, date_from, date_to)

   ...