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