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.
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.
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.
“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