How to update foreign-key constraints changed when Alembic cannot detect them?

[richardnguyen99]

I have two models looking like this:

# University tables
class University(db.Model):
    """Core university model"""

    __tablename__ = "universities"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)

    short_name: Mapped[str] = mapped_column(
        String(16),
        nullable=False,
        unique=True,
        index=True,
    )

    full_name: Mapped[str] = mapped_column(
        String(128),
        unique=True,
        nullable=False,
    )

    # ... more fields

    campuses: Relationship[list["UniversityCampus"]] = relationship(
        "UniversityCampus",
        backref="university",
        lazy=True,
        collection_class=list,
    )


# University-campus tables
class UniversityCampus(db.Model):
    """Normalized University Campus model"""

    __tablename__ = "university_campuses"
    __table_args__ = (
        UniqueConstraint(
            "address_street",
            "address_city",
            "school_short_name",
        ),
    )

    address_id: Mapped[int] = mapped_column(
        db.Integer, primary_key=True, autoincrement=True
    )

    address_street: Mapped[str] = mapped_column(db.String(128), nullable=False)

    address_city: Mapped[str] = mapped_column(db.String(64), nullable=False)

    address_state: Mapped[str] = mapped_column(db.String(64), nullable=True)

    address_zip_code: Mapped[str] = mapped_column(
        db.String(16), nullable=False
    )

    # ... more fields

    school_short_name: Mapped[str] = mapped_column(
        String(32),
        ForeignKey("universities.short_name"),
        nullable=False,
    )

With current models, they prevent me from updating short_name in raw SQL due to violation of foreign key constraint of school_short_name. I want to update it on schema level by adding these options:

# university model
class University(db.Model):
    # ... other fields
    campuses: Relationship[list["UniversityCampus"]] = relationship(
        "UniversityCampus",
        backref="university",
        lazy=True,
        collection_class=list,
        cascade="all, delete-orphan",
        passive_deletes=True,
    )

# university-campus model
class UniversityCampus(db.Model):
    school_short_name: Mapped[str] = mapped_column(
        String(32),
        ForeignKey("universities.short_name"),
        nullable=False,
        onupdate="CASCADE",
    )

However, alembic does not detect these constraint changes. When I need to update something related to short_name, I need to manually drop the constraints and re-add them again, which is tedious because I have many other relations like these. Is there anyway I can achieve this with Python and Alembic?

[CaselIT]

Hi,

onupdate is an option of the ForeignKey not of a mapped_column

[mmiller8878]

I don't want to hijack this thread but I think I am also having this issue - alembic appears not to detect Foreign key changes, or foreign keys at all in my case.

I know this was an issue in some earlier versions of alembic, but according to the docs, foreign keys should be automatically handled.

I have a table called members and a foreign key defined like so:

    fk_signup_code: Mapped[Optional[str]] = mapped_column(
        ForeignKey("signup_codes.code", name="fk_signup_code")
    )

when I change the FK to point at a different column in the other table:

    fk_signup_code_id: Mapped[Optional[str]] = mapped_column(
        ForeignKey("signup_codes.id", name="fk_signup_code_id")
    )

running alembic check produces the following output:

ERROR [alembic.util.messaging] New upgrade operations detected: [('add_column', None, 'members', Column('fk_signup_code_id', String(), table=<members>)), ('remove_column', None, 'members', Column('fk_signup_code', VARCHAR(), table=<members>))]

So it's correctly detecting the change in column name but not the foreign key. If I don't change the column name, it doesn't detect any changes.

I'm using alembic 1.13.1.

Is this expected behaviour @CaselIT ?

Thanks

[CaselIT]

I think this mostly depends on the database being used. it should detect changes to fk.

If you have more information please open a new discussion