Flask-migrate: Create foreign key with name None leads to fail to downgrade

[neekey]

Hi, I recently added a new foreignKey to my existing table:

workspace_id = db.Column(db.Integer(), db.ForeignKey('workspaces.id'), nullable=True)
workspace = db.relationship('Workspace',
                                foreign_keys=[workspace_id],
                                backref=db.backref('queries',
                                                   cascade='all, delete-orphan'))

And the migrate script was like below:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column(u'queries', sa.Column('workspace_id', sa.Integer(), nullable=True))
    op.create_foreign_key(None, 'queries', 'workspaces', ['workspace_id'], ['id'])
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'queries', type_='foreignkey')
    op.drop_column(u'queries', 'workspace_id')
    ### end Alembic commands ###

When I try to do downgrade, I got error message:

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint ForeignKeyConstraint(<sqlalchemy.sql.base.ColumnCollection object at 0x7ff67248f550>, None, table=Table('queries', MetaData(bind=None), schema=None)); it has no name

Not sure how should I give a name to it

[miguelgrinberg]

@neekey have you read the note in the documentation regarding the name argument to create_foreign_key? Here is the link: http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.create_foreign_key.params.name

[neekey]

@miguelgrinberg thanks for quick reply! I didn't know that and I guessed that the name is essential to the operation.

So does that mean that before I do any migration to the database, I should manually update the migrate script to give it a name? Is there's a way I can specific the name in my model declaration? Or is it possible that Flask-Migrate creates a random name?

[miguelgrinberg]

@neekey Flask-Migrate does not really get involved with migration scripts, this is all done by Alembic. Have a read at this section of the documentation, I think it will tell you how you can address this problem.

[neekey]

@miguelgrinberg Thanks for your info, solved my problem, and actually, I found there's guideline in Flask-SQLAlchemy's doc as well http://flask-sqlalchemy.pocoo.org/2.1/config/#using-custom-metadata-and-naming-conventions

[kenessajr]

Hi @neekey, I follow the steps in the link you shared http://flask-sqlalchemy.pocoo.org/2.1/config/#using-custom-metadata-and-naming-conventions and managed to remove this error

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint ForeignKeyConstraint(<sqlalchemy.sql.base.ColumnCollection object at 0x7ff67248f550>, None, table=Table('queries', MetaData(bind=None), schema=None)); it has no name.

now i'm getting another error which is

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) constraint “fk_profiles_modified_by_id_users” of relation “profiles” does not exist
E        [SQL: ‘ALTER TABLE profiles DROP CONSTRAINT fk_profiles_modified_by_id_users’] (Background on this error at: http://sqlalche.me/e/f405)

This is my model i'm doing

class Profile(AwareModel):
    """Define the user profile table"""
    names = db.Column(db.String(255), nullable=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('users.id'))
    user = db.relationship('User',
                           backref=db.backref('profiles', uselist=False, cascade='all, delete-orphan'))

and this is the Alembic Script

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('profiles',
    sa.Column('created_at', sqlalchemy_utils.types.arrow.ArrowType(), nullable=False),
    sa.Column('modified_at', sqlalchemy_utils.types.arrow.ArrowType(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('active', sa.Boolean(), nullable=True),
    sa.Column('names', sa.String(length=255), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('modified_by_id', sa.Integer(), nullable=True),
    sa.Column('created_by_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['created_by_id'], ['users.id'], name=op.f('fk_profiles_created_by_id_users'), use_alter=True),
    sa.ForeignKeyConstraint(['modified_by_id'], ['users.id'], name=op.f('fk_profiles_modified_by_id_users'), use_alter=True),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], name=op.f('fk_profiles_user_id_users')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_profiles'))
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('profiles')
    # ### end Alembic commands ###

[miguelgrinberg]

This issue will be automatically closed due to being inactive for more than six months. Seeing that I haven't responded to your last comment, it is quite possible that I have dropped the ball on this issue and I apologize about that. If that is the case, do not take the closing of the issue personally as it is an automated process doing it, just reopen it and I'll get back to you.

[JCurtisRMIT]

For anyone finding this and at a loss how to progress - some info in the issue linking this above led me to find that the FKey you may have named may not match the actual FKey string you need to provide to the op.drop_constraint() method in your migration script to downgrade.

To get the real FKey string this SQL query may be useful (it was for me!)

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = <your-schema-name>
             AND rel.relname = <your-table-name>;

That should pull up the correct FKey string you need to provide op.drop_constraint() method in your migration script to downgrade. :slightly_smiling_face: