Alembic SQLite ALTER TABLE with a foreign key for self-binding

Question

Alembic transition for SQLite database:

def upgrade(): with op.batch_alter_table('my_table') as batch_op: batch_op.add_column(sa.Column('parent_id', sa.String(24))) batch_op.create_foreign_key('parent_constraint', 'my_table', ['parent_id'], ['id']) 

which should create a foreign key parent_id , referencing the id the same table my_table , creates a link to a table named _alembic_batch_temp :

 CREATE TABLE "my_table" ( id VARCHAR(24) NOT NULL, parent_id VARCHAR(24), PRIMARY KEY (id), CONSTRAINT parent_constraint FOREIGN KEY(parent_id) REFERENCES _alembic_batch_temp (id) ) 

How to create restrictions for self-regulation when changing a table?

Answer 1

After some research, I found that the problem here is how Alembic performs batch migration. In short, in the current version (0.7.6) of Alembic, it is not possible to create a relationship with itself through migration.

• As described in the Alebic documentation

  To avoid this situation, you can manually create a batch migration:

• Rename the old table to some temporary name:

   ALTER TABLE my_table RENAME TO migration_temp_table; 

Create a new table with the correct name and the correct link:

 CREATE TABLE my_table ( id VARCHAR(24) NOT NULL, parent_id VARCHAR(24), PRIMARY KEY (id), CONSTRAINT parent_constraint FOREIGN KEY(parent_id) REFERENCES my_table (id) ) 

Copy data:

 INSERT INTO my_table (id) SELECT id FROM migration_temp_table; 

Delete the old table:

 DROP TABLE migration_temp_table;