Alembic does not make composite primary key

Question

I had a SQLAlchemy model like -

class UserFavPlace(db.Model):
    # This model stores the feedback from the user whether he has
    # faved a place or not
    __tablename__ = u'user_fav_places'

    id = db.Column(db.Integer, primary_key = True)
    public_place_id = db.Column(db.Integer, db.ForeignKey(u'public_places.id'))
    user_id = db.Column(db.Integer, db.ForeignKey(u'users.user_id'))
    fav = db.Column(db.Boolean)
    updated_time = db.Column(db.DateTime)

    place = relationship(u'PublicPlace', backref = u'user_fav_places')
    user = relationship(u'User', backref = u'user_fav_places')

And then I changed this model to the following -

class UserFavPlace(db.Model):
    # This model stores the feedback from the user whether he has
    # faved a place or not
    __tablename__ = u'user_fav_places'

    public_place_id = db.Column(db.Integer, db.ForeignKey(u'public_places.id'),
            primary_key = True)
    user_id = db.Column(db.Integer, db.ForeignKey(u'users.user_id'),
            primary_key = True)
    fav = db.Column(db.Boolean)
    updated_time = db.Column(db.DateTime)

    place = relationship(u'PublicPlace', backref = u'user_fav_places')
    user = relationship(u'User', backref = u'user_fav_places')

However, Alembic is not generating the correct upgrade and downgrade statements. Seems like it is not adding the newly introduced primary key constraints.

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('user_fav_places', 'id')
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user_fav_places', sa.Column('id', mysql.INTEGER(display_width=11), nullable=False))
    ### end Alembic commands ###

I am not sure on how to add this.

score 7 · Accepted Answer · edited Aug 22 '18 at 09:18

7

I find the autogeneration to be pretty bad about detecting primary/foreign key changes. Which is okay, usually you want to customize those anyway.

I haven't created composite primary keys, but it looks like this should work based on the doc:

http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.create_primary_key

op.create_primary_key(
        "pk_user_fav_places", "user_fav_places",
        ["public_place_id", "user_id"]
    )

However, you might run into a different problem, which I wrote up here: Adding primary key to existing MySQL table in alembic

This might have been fixed in more recent versions of alembic, but it may not let you create primary keys on existing tables (see the answer to above for why). If that happens, you can just craft the SQL yourself and run it with op.execute.

Hope that helps!

edited Aug 22 '18 at 09:18

Jarvl

67
8

answered Mar 13 '15 at 14:30

Rachel Sanders

5,734
1
27
36

Corrected reference to the Alembic documentation: http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.create_primary_key – Screamer Oct 17 '16 at 09:40
By the way, make sure that if you already have data in your DB there is no duplication. In case there is, head over to https://stackoverflow.com/questions/2230295/whats-the-best-way-to-dedupe-a-table – Flavian Hautbois Apr 22 '19 at 21:18

Alembic does not make composite primary key

1 Answers1