CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemyalembicflask-migrate
Stephen Graham
Stephen Graham

Reputation: 439

Alembic recreates foreign keys every time I migrate causing duplicate foreign keys on my tables

I have a flask app using sql-alchemy and flask migrate to handle database changes. Every time I run a flask migrate to create a script for Alembic to update the database, the script contains commands to create foreign keys that already exist in my database.

The table definition in my models.py is

class Airline(db.Model):

    __tablename__ = 'Airlines'
    AirlineID =         db.Column(db.Integer,       primary_key=True)
    AirlineShortCode =  db.Column(db.String(3),     index=True, unique=True, nullable=False)
    FullName =          db.Column(db.String(256),   unique=False, nullable=True)
    ShortName =         db.Column(db.String(64),    unique=False, nullable=True)

class CabinClass(db.Model):

    __tablename__ = 'CabinClasses'
    CabinClassID =         db.Column(db.Integer,     primary_key=True)
    AirlineShortCode =     db.Column(db.ForeignKey("Airlines.AirlineShortCode"), nullable=True)
    CabinClassShortCode =  db.Column(db.String(32),  unique=False, nullable=False)
    CabinClassName =       db.Column(db.String(64),  unique=False, nullable=True)

The line in the migration database update script that is generated to create the foreign key is

    op.create_foreign_key(None, 'CabinClasses', 'Airlines', ['AirlineShortCode'], ['AirlineShortCode'])

This line is generated every time I create the migration script, resulting in multiple foreign key entries in the CabinClasses table:

enter image description here

I see that the name of each foreign key created is different and that the create_foreign_key command in the database migration script states the name as None. I believe this is correct if you are using an automated naming scheme, which I believe is what happens by default

For setups that use an automated naming scheme such as that described at Configuring Constraint Naming Conventions, name here can be None, as the event listener will apply the name to the constraint object when it is associated with the table

https://alembic.sqlalchemy.org/en/latest/naming.html

Can anyone identify what would cause these foreign keys to be created every time I update the database?

Upvotes: 2

Views: 2986

Answers (1)

Miguel Grinberg
Miguel Grinberg

Reputation: 67489

The names of the constraints that you are getting look like they come from your database, not SQLAlchemy. You need to add the constraint naming templates for all types of constraints to the SQLAlchemy metadata, and then I think you will get consistent names. See how to do this in the Flask-SQLAlchemy documentation. I'm copying the code example from the docs below for your convenience:

from sqlalchemy import MetaData
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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"
}

metadata = MetaData(naming_convention=convention)
db = SQLAlchemy(app, metadata=metadata)

Upvotes: 1

Related Questions