There is a database with two primary_key in the columns project_id and endpoint . I need to swap primary_key between two columns

 class ProjectEndpointPermission(db.Model): __tablename__ = 'project_endpoint_permission' project_id = db.Column(db.Integer, db.ForeignKey('project.id'), primary_key=True) project = db.relationship("Project", backref=db.backref("permissions", lazy="dynamic", cascade='all, delete, delete-orphan')) endpoint = db.Column(db.String(50), primary_key=True) group_id = db.Column(db.Integer, db.ForeignKey('group.id')) group = db.relationship("Group", backref=db.backref("endpoints", uselist=False, cascade="all, delete, delete-orphan"))

In the model file, I deleted primary_key from one column and put it in another. Made a migration, but she did not see the changes:

 project_id = db.Column(db.Integer, db.ForeignKey('project.id')) group_id = db.Column(db.Integer, db.ForeignKey('group.id'), primary_key=True)

I did the migration using python manage.py db migrate . I use MySQL .

In the migration file, I manually made the migration:

 def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.drop_constraint(None, 'project_endpoint_permission', type_='primary') op.create_primary_key(None, 'project_endpoint_permission', ['group_id','endpoint' ]) ### end Alembic commands ### def downgrade(): ### commands auto generated by Alembic - please adjust! ### op.drop_constraint(None, 'project_endpoint_permission', type_='primary') op.create_primary_key(None, 'project_endpoint_permission', ['project_id', 'endpoint']) ### end Alembic commands ##

But when I run the upgrade I get the error: op.drop_constraint('primary', 'project_endpoint_permission', type_='primary') File "<string>", line 8, in drop_constraint File "<string>", line 3, in drop_constraint sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1025, 'Error on rename of \'.\\dashboard\\#sql-b50_6d\' to \'.\\dashboard\\project_endpoint_permission\' (errno: 150 "Foreign key constraint is incorrectly formed")') [SQL: 'ALTER TABLE project_endpoint _permission DROP PRIMARY KEY ']

Help me rewrite the primary key in the columns of one table.

    1 answer 1

    Try during the migration script (upgrade) to follow instructions such as creating an additional column, writing keys to it, hanging the primary flag on it, and then deleting the first primary_key column.

    To communicate with the session (to do data migrations, etc.) during the migration script, you can use

     conn = op.get_bind() Session = sa.orm.sessionmaker() session = Session(bind=conn) session.add(instance) session.flush()