Multiple primary keys in ecto

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 so I guess syntax is right. But it won’t work in a new migration.

Any suggestions or workaround ?
Thanks.

You have to change the old primary key to include the new column.

This might cause conflicts and has to be considered a destructive operation.

This has nothing to do with ecto, but is pure database. I’m not aware of any database that allows to have more than one primary key, but as you stated correctly, you can combine multiple columns into a single compound primary key.

1 Like

Hi thanks for your reply. You are right data bases only support single primary keys. But we can make them composite . 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.

My question what I have to do to make it work in my new migration.

Drop the old and create a new primary key containing the necessary columns. Depending on the size of your database this might take a very long time and again, this might cause conflicts.

3 Likes

“Drop the old primary key and”, so you don’t wipe the table. And yeah, if there are conflicts after generating the new compound primary key you will get those conflict issues at migration time. And since creating the new compound primary key involves a linear scan it can be fairly slow when there is a lot of data.

I had to make a change to the primary key of a table to add a default value.
This solution worked for me:

def up do
  execute "ALTER TABLE table_name DROP CONSTRAINT table_name_pkey"

  flush()

  alter table(:table_name) do
    modify :id, :binary_id,
      primary_key: true,
      default: fragment("gen_random_uuid()"),
      null: false
  end
end

def down do
  execute "ALTER TABLE table_name DROP CONSTRAINT table_name_pkey"

  flush()

  alter table(:installations_history) do
    modify :id, :binary_id, primary_key: true, null: false
  end
end