SQLAlchemy association table – cascade delete issue /u/Urban_II Python Education

Hello all, I’m having trouble with my database design using SQLAlchemy. I have two tables: User, and Upgrade, and an association table between them. A User can own multiple Upgrades, and an Upgrade can have multiple owners. When a User is deleted, all rows from the association table for what that User owns should be deleted, likewise when an Upgrade is deleted, all rows in the association table for Users which own that Upgrade should be deleted.

Currently, I have ondelete=’CASCADE’ set for both foreign keys in the association table. However, when I delete either a User or an Upgrade, the row remains in the association table referencing the deleted parent. Here is my model code, can anyone advise? Thank you very much.

class User(UserMixin, db.Model): db.__tablename__ = 'User', id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(100), unique=True) password = db.Column(db.String(100)) name = db.Column(db.String(1000)) xp = db.Column(db.Integer) timespent = db.Column(db.Integer) upgrades = db.relationship('Upgrade', secondary="upgrade_owners", back_populates="users") class Upgrade(db.Model): db.__tablename__ = 'Upgrade', id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(1000)) multiplier = db.Column(db.Float) price = db.Column(db.Integer) users = db.relationship('User', secondary="upgrade_owners", back_populates="upgrades") upgrade_owners = db.Table( "upgrade_owners", db.Column("user_id", db.Integer, db.ForeignKey(User.id, ondelete='CASCADE')), db.Column("upgrade_id", db.Integer, db.ForeignKey(Upgrade.id, ondelete='CASCADE')), ) 

Hello all, I’m having trouble with my database design using SQLAlchemy. I have two tables: User, and Upgrade, and an association table between them. A User can own multiple Upgrades, and an Upgrade can have multiple owners. When a User is deleted, all rows from the association table for what that User owns should be deleted, likewise when an Upgrade is deleted, all rows in the association table for Users which own that Upgrade should be deleted.

Currently, I have ondelete=’CASCADE’ set for both foreign keys in the association table. However, when I delete either a User or an Upgrade, the row remains in the association table referencing the deleted parent. Here is my model code, can anyone advise? Thank you very much.

class User(UserMixin, db.Model): db.__tablename__ = 'User', id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(100), unique=True) password = db.Column(db.String(100)) name = db.Column(db.String(1000)) xp = db.Column(db.Integer) timespent = db.Column(db.Integer) upgrades = db.relationship('Upgrade', secondary="upgrade_owners", back_populates="users") class Upgrade(db.Model): db.__tablename__ = 'Upgrade', id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(1000)) multiplier = db.Column(db.Float) price = db.Column(db.Integer) users = db.relationship('User', secondary="upgrade_owners", back_populates="upgrades") upgrade_owners = db.Table( "upgrade_owners", db.Column("user_id", db.Integer, db.ForeignKey(User.id, ondelete='CASCADE')), db.Column("upgrade_id", db.Integer, db.ForeignKey(Upgrade.id, ondelete='CASCADE')), )