The Alembic migration for a 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 is supposed to create a foreign key parent_id
referencing id
of the same table my_table
, creates a reference to a table called _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 self-referencing constraints when altering a table?
After some research I found that the problem here is the way Alembic does the batch migration. In short, at the current version (0.7.6) of Alembic it's not possible to create relation with self by migration.
As described in the Alembic documentation, to do the migration, new table is created with a temporary name and changes from the alter table code. In this case:
The table is filled with the data from the old table:
Then the old table is removed:
Finally the newly created table is renamed to it's proper name:
The problem with this way of doing things is already visible in the first code snippet. The newly created foreign key is referencing the temporary table and once it's created it can't be changed due to restrictions in SQLite. So after the renaming of the table you end up with the table you provided:
To Avoid this situation you can create the batch migration manually:
Rename the old table to some temporary name:
Create new table with proper name and proper reference:
Copy the data:
Remove the old table: