sqlalchemy
Autogenerated alembic script to drop_constraint is missing the constraints name.
Describe the bug When using the autogenerate option to generate a migrate script, the generated code to drop constraints in rollback does not work, if the constraint name is generated by the database.,
Expected behavior The generation process should somehow warn the user or at least add a comment with a clear warning to the script that downgrade will not work. Since we cannot know how the key will be named, this can't be fixed, but improved.
To Reproduce When having two tables with a new ForeignKey:
class DataPoint(Base):
__tablename__ = "data_points"
id = Column(Integer, primary_key=True)
change_user_id = Column(Integer, ForeignKey("users.change_user_id") # new ForeignKey
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
The generated code to create the ForeignKey looks like this (which is ok, we use the db to assign a name for the key):
op.create_foreign_key(
None, "data_points", "users", ["change_user_id"], ["id"]
)
The generated code to create the ForeignKey looks like this (which can't run, since we need the name to drop the constraint):
op.drop_constraint(None, "package_prices", type_="foreignkey")
Versions.
- OS:
- Python:
- Alembic: 1.7.1
- SQLAlchemy:
- Database: MS SQL
- DBAPI:
Additional context
Have a nice day!
I think a comment should be generated in the code to this effect, and overall when there are one or more warning comments in the code, the autogenerate command itself can produce a console warning. this way when you review your migrations you have some indication of spots that you definitely need to address.
I have a similar issue. Autogenerate also generates None in the drop_constrains function, but by using explicitly naming foreign keys using a naming convention (got there from this https://github.com/sqlalchemy/alembic/issues/588 issue) I was able to create proper names which solves the upgrading and downgrading issue.
MetaData(schema="my_schema",
naming_convention={"ix": "ix_%(column_0_label)s",
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"},
)
BUT: When I do an autogenerate without changing anything in the code Alembic changes the foreignkeys. I can run autogenerate multiple times and the new revisions looks like the following:
"""change1
Revision ID: de0b721b57b8
Revises: 2272f316e54a
Create Date: 2022-01-25 16:32:43.697034
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = 'de0b721b57b8'
down_revision = '2272f316e54a'
branch_labels = None
depends_on = None
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint('fk_bookings_member_id_members', 'bookings', type_='foreignkey')
op.drop_constraint('fk_bookings_voucher_id_vouchers', 'bookings', type_='foreignkey')
op.create_foreign_key(op.f('fk_bookings_member_id_members'), 'bookings', 'members', ['member_id'], ['member_id'], source_schema='my_schema', referent_schema='my_schema')
op.create_foreign_key(op.f('fk_bookings_voucher_id_vouchers'), 'bookings', 'vouchers', ['voucher_id'], ['voucher_id'], source_schema='my_schema', referent_schema='my_schema')
op.drop_constraint('fk_trainings_partner_event_id_partners', 'trainings', type_='foreignkey')
op.create_foreign_key(op.f('fk_trainings_partner_event_id_partners'), 'trainings', 'partners', ['partner_event_id'], ['event_id'], source_schema='my_schema', referent_schema='my_schema')
op.drop_constraint('fk_travelbookings_client_id_travelclients', 'travelbookings', type_='foreignkey')
op.create_foreign_key(op.f('fk_travelbookings_client_id_travelclients'), 'travelbookings', 'travelclients', ['client_id'], ['client_id'], source_schema='my_schema', referent_schema='my_schema')
op.drop_constraint('fk_travelclients_member_id_members', 'travelclients', type_='foreignkey')
op.create_foreign_key(op.f('fk_travelclients_member_id_members'), 'travelclients', 'members', ['member_id'], ['member_id'], source_schema='my_schema', referent_schema='my_schema')
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(op.f('fk_travelclients_member_id_members'), 'travelclients', schema='my_schema', type_='foreignkey')
op.create_foreign_key('fk_travelclients_member_id_members', 'travelclients', 'members', ['member_id'], ['member_id'])
op.drop_constraint(op.f('fk_travelbookings_client_id_travelclients'), 'travelbookings', schema='my_schema', type_='foreignkey')
op.create_foreign_key('fk_travelbookings_client_id_travelclients', 'travelbookings', 'travelclients', ['client_id'], ['client_id'])
op.drop_constraint(op.f('fk_trainings_partner_event_id_partners'), 'trainings', schema='my_schema', type_='foreignkey')
op.create_foreign_key('fk_trainings_partner_event_id_partners', 'trainings', 'partners', ['partner_event_id'], ['event_id'])
op.drop_constraint(op.f('fk_bookings_voucher_id_vouchers'), 'bookings', schema='my_schema', type_='foreignkey')
op.drop_constraint(op.f('fk_bookings_member_id_members'), 'bookings', schema='my_schema', type_='foreignkey')
op.create_foreign_key('fk_bookings_voucher_id_vouchers', 'bookings', 'vouchers', ['voucher_id'], ['voucher_id'])
op.create_foreign_key('fk_bookings_member_id_members', 'bookings', 'members', ['member_id'], ['member_id'])
# ### end Alembic commands ###
`
``
@HansBambel that is a separate behavior which looks like you are getting false positives on foreign key constraint comparisons. that's an autogenerate detection issue and it's highly dependent on the database in use. if I had to guess I'd say there's maybe a casing convention mismatch or something like that happening, but cant be sure without complete details (start a new discussion).