sqlite rollback fails when primary key is changed and old pk column is dropped

[jayvdb]

We had a table with two id column, and switched the pk from one id to the other id column, and dropped the old id column.

i.e. before

CREATE TABLE Foo (
id_not_needed TEXT NOT NULL PRIMARY KEY,
id TEXT NOT NULL ,
blah TEXT 
);

after

CREATE TABLE Foo (
id TEXT NOT NULL  PRIMARY KEY ,
blah TEXT 
);

The migration works well.
However rollback fails because it starts with

ALTER TABLE Foo ADD COLUMN id_not_needed TEXT NOT NULL PRIMARY KEY DEFAULT null;
...

This fails with

Encountered unexpected error: Sqlite error Cannot add a PRIMARY KEY column

I havent checked postgres - quite possible it has a similar problem.

Then, even if the DDL is fixed, there is still the fact that recreating the old pk column cant become the primary key unless the the recreated column is given values for the existing rows, and how to populate that field cant easily be automatically generated.