[aerich migrate] PostgreSQL: Cannot drop constraint after deleting FK on a model #204
Description
Abstract
I use docker-compose for running my code.
I have a model with a useless FK (since another model have this FK by extension).
I've already done a migration via aerich before, so all my tables are generated.
I've deleted the FK on the model, then I've ran aerich migrate --name remove_committee_fk_field, which generate the migration file.
I've ran aerich upgrade.
I got a traceback with the error tortoise.exceptions.OperationalError: constraint "fk_recordse_committe_b8357190" of relation "recordselectionmodel" does not exist.
After further investigation, the problem came from the SQL code generated by the migrate command.
The upgrade code try to drop a constraint that doesn't exist, without checking if it exist in the first place.
Code
Before modifiation
from tortoise.models import Model
from tortoise import fields
class RecordSelectionModel(Model):
class Meta:
tablename = "record_selections"
unique_together = ("record", "committee_archive")
id = fields.IntField(pk=True)
committee = fields.ForeignKeyField("models.CommitteeModel", related_name="record_selections")
record = fields.ForeignKeyField("models.RecordModel", related_name="record_selections")
committee_archive = fields.ForeignKeyField("models.CommitteeArchiveModel", related_name="record_selections")
After modification
from tortoise.models import Model
from tortoise import fields
class RecordSelectionModel(Model):
class Meta:
tablename = "record_selections"
unique_together = ("record", "committee_archive")
id = fields.IntField(pk=True)
record = fields.ForeignKeyField("models.RecordModel", related_name="record_selections")
committee_archive = fields.ForeignKeyField("models.CommitteeArchiveModel", related_name="record_selections")
Command executed
aerich migrate --name remove_committee_fk_field
aerich upgrade
Expected behavior
Deleting the constraint in the table.
Result
Traceback (most recent call last):
File "/usr/local/bin/aerich", line 8, in <module>
sys.exit(main())
File "/usr/local/lib/python3.9/site-packages/aerich/cli.py", line 258, in main
cli()
File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1128, in __call__
return self.main(*args, **kwargs)
File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1053, in main
rv = self.invoke(ctx)
File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1659, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1395, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/usr/local/lib/python3.9/site-packages/click/core.py", line 754, in invoke
return __callback(*args, **kwargs)
File "/usr/local/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func
return f(get_current_context(), *args, **kwargs)
File "/usr/local/lib/python3.9/site-packages/aerich/cli.py", line 33, in wrapper
loop.run_until_complete(f(*args, **kwargs))
File "/usr/local/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
return future.result()
File "/usr/local/lib/python3.9/site-packages/aerich/cli.py", line 102, in upgrade
migrated = await command.upgrade()
File "/usr/local/lib/python3.9/site-packages/aerich/__init__.py", line 53, in upgrade
await conn.execute_script(upgrade_query)
File "/usr/local/lib/python3.9/site-packages/tortoise/backends/asyncpg/client.py", line 38, in translate_exceptions_
raise OperationalError(exc)
tortoise.exceptions.OperationalError: constraint "fk_recordse_committe_b8357190" of relation "recordselectionmodel" does not exist
SQL file generated by migrate command
-- upgrade --
ALTER TABLE "recordselectionmodel" DROP CONSTRAINT "fk_recordse_committe_b8357190";
ALTER TABLE "recordselectionmodel" DROP COLUMN "committee_id";
CREATE UNIQUE INDEX "uid_recordselec_record__699c3f" ON "recordselectionmodel" ("record_id", "committee_archive_id");
-- downgrade --
DROP INDEX "uid_recordselec_record__699c3f";
ALTER TABLE "recordselectionmodel" ADD "committee_id" INT NOT NULL;
ALTER TABLE "recordselectionmodel" ADD CONSTRAINT "fk_recordse_committe_b8357190" FOREIGN KEY ("committee_id") REFERENCES "committees" ("id") ON DELETE CASCADE;
Solution (what the SQL generated should have been)
-- upgrade --
ALTER TABLE "recordselectionmodel" DROP CONSTRAINT IF EXISTS "fk_recordse_committe_b8357190";
ALTER TABLE "recordselectionmodel" DROP COLUMN "committee_id";
CREATE UNIQUE INDEX "uid_recordselec_record__699c3f" ON "recordselectionmodel" ("record_id", "committee_archive_id");
-- downgrade --
DROP INDEX "uid_recordselec_record__699c3f";
ALTER TABLE "recordselectionmodel" ADD "committee_id" INT NOT NULL;
ALTER TABLE "recordselectionmodel" ADD CONSTRAINT "fk_recordse_committe_b8357190" FOREIGN KEY ("committee_id") REFERENCES "committees" ("id") ON DELETE CASCADE;
So I think you should add an if exist condition on the drop constraint.
Really nice job otherwise.