python - Add onupdate="cascade" to existing column in postgres with sqlalchemy

Question

I want to add onupdate="cascade" on the ForeignKey object in an existing table.

I have a many-to-many relationship using an association object:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship

postgres_url = "postgresql:/username:pwd@localhost/myDB"
engine = create_engine(postgres_url)
Base = declarative_base()
Base.metadata.create_all(engine)

class VehicleType(Base):
    __tablename__ = 'vehicleTypes'

    name = Column(String, primary_key=True)

class SearchTermToVehicleType(Base):
    __tablename__ = "searchTermToVehicleType"

    searchTermName = Column(String, ForeignKey('searchTerms.name'), primary_key=True)
    vehicleTypeName = Column(String, ForeignKey('vehicleTypes.name'), primary_key=True)

class SearchTerm(Base):
    __tablename__ = 'searchTerms'

    name = Column(String, primary_key=True)
    vehicleTypes = relationship("VehicleType", secondary="searchTermToVehicleType")

This is already in production, but now I need to allow users to change the search term name. It seems that with an association table sqlalchemy automagically does ondelete="cascade" but not onupdate="cascade" so now I want to change my models to include this on the ForeignKey object on the association table so that I can simply change the name of any searchTerm.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship

postgres_url = "postgresql:/username:pwd@localhost/myDB"
engine = create_engine(postgres_url)
Base = declarative_base()
Base.metadata.create_all(engine)

class VehicleType(Base):
    __tablename__ = 'vehicleTypes'

    name = Column(String, primary_key=True)

class SearchTermToVehicleType(Base):
    __tablename__ = "searchTermToVehicleType"

    searchTermName = Column(
        String, 
        ForeignKey('searchTerms.name', onupdate="cascade"), 
        primary_key=True
    )
    vehicleTypeName = Column(String, ForeignKey('vehicleTypes.name'), primary_key=True)

class SearchTerm(Base):
    __tablename__ = 'searchTerms'

    name = Column(String, primary_key=True)
    vehicleTypes = relationship("VehicleType", secondary="searchTermToVehicleType")

But this will not change the existing underlying postgres tables. I can drop the SearchTerm and SearchTermToVehicleType tables and let sqlalchemy build them again, but I would prefer not to have to repopulate them.

The docs mention that this uses the database's ON UPDATE CASCADE functionality. How can I alter the existing table or column to achieve the result of the above? Or do I have to drop and rebuild the tables?

question from:https://stackoverflow.com/questions/66058247/add-onupdate-cascade-to-existing-column-in-postgres-with-sqlalchemy

Answer 1

You need to run DROP CONSTRAINT and ADD CONSTRAINT sql commands on your postgresql database to change your existing constraint to add on update cascade.

ALTER TABLE ...
DROP CONSTRAINT ...,
ADD CONSTRAINT ... FOREIGN KEY ... ON UPDATE CASCADE;

To automate schema management - please check out Alembic project.