Here's the setup:

A User can have many images assigned (related_images), but can only have 1 profile image.

class Photo(Model):
    __tablename__ = "photo"

    id = Column(Integer, primary_key=True, autoincrement=True)
    filepath = Column(Text)

    user_id = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"))
    user = db.relationship("User", foreign_keys=[user_id], back_populates="related_images", lazy=True)

class User(Model):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Text)
    
    related_images = db.relationship("Photo", foreign_keys=[Photo.user_id], back_populates="user", lazy=True, cascade="delete")

    profile_image_id = Column(Integer, ForeignKey("photo.id", ondelete="CASCADE"))
    profile_image = db.relationship("Photo", foreign_keys=[profile_image_id], lazy=True, post_update=True, cascade="delete")

I want to delete a User's profile image, so I do:

user = get_user(user_id)
db.session.delete(user.profile_image)
db.sessionmit()

This will successfully delete the row from the Photo table, however user.profile_image_id on the User table will still hold the old photo id, id 1 in this case.

How do I delete the user's profile photo so that it's removed (or no longer referenced) from the User table too? The ondelete and cascade='delete' don't seem to work here.

Here's the setup:

A User can have many images assigned (related_images), but can only have 1 profile image.

class Photo(Model):
    __tablename__ = "photo"

    id = Column(Integer, primary_key=True, autoincrement=True)
    filepath = Column(Text)

    user_id = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"))
    user = db.relationship("User", foreign_keys=[user_id], back_populates="related_images", lazy=True)

class User(Model):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Text)
    
    related_images = db.relationship("Photo", foreign_keys=[Photo.user_id], back_populates="user", lazy=True, cascade="delete")

    profile_image_id = Column(Integer, ForeignKey("photo.id", ondelete="CASCADE"))
    profile_image = db.relationship("Photo", foreign_keys=[profile_image_id], lazy=True, post_update=True, cascade="delete")

I want to delete a User's profile image, so I do:

user = get_user(user_id)
db.session.delete(user.profile_image)
db.sessionmit()

This will successfully delete the row from the Photo table, however user.profile_image_id on the User table will still hold the old photo id, id 1 in this case.

How do I delete the user's profile photo so that it's removed (or no longer referenced) from the User table too? The ondelete and cascade='delete' don't seem to work here.

• python
• python-3.x
• sqlalchemy
• flask-sqlalchemy

• The title specifies a one-to-many relationship, but each table has an FK to the other. Please edit the question to clarify how you expect the relationship to behave. – snakecharmerb Commented Nov 17, 2024 at 14:59
• Hi, I edited my question. Basically a user has related images, but only a single profile image. In order to reuse the Photo model, I need to define an FK on the User model, so that's why there are two FKs. However, the issue is when deleting the profile photo only, the FK on the User is not cleared. If this isn't the way to set up the Models, it'll be helpful for me to see what the proper model setup should be. – doejoe Commented Nov 17, 2024 at 17:37

1 Answer 1

class Photo(Model): tablename = "photo"

id = Column(Integer, primary_key=True, autoincrement=True)
filepath = Column(Text)

user_id = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"))
user = db.relationship("User", foreign_keys=[user_id], back_populates="related_images", lazy='select')

class User(Model): tablename = "user"

id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Text)

related_images = db.relationship("Photo", foreign_keys=[Photo.user_id], back_populates="user", lazy='select', cascade="all, delete-orphan")

profile_image_id = Column(Integer, ForeignKey("photo.id", ondelete="SET NULL"), nullable=True)
profile_image = db.relationship("Photo", foreign_keys=[profile_image_id], lazy='joined', post_update=True, cascade="all, delete-orphan")