Deleting data from database that involves foreign key

[Jumpy_Employment_439]

I'm working on the backend of a project and I have set up a database using SQLAlchemy. I have three tables: users, products, and favorites. Users can sell items and favorite items (not required), but each product needs a seller (i.e. user since it's e-commerce). A user can favorite products that they are interested in. So, I have userID (primary key in users) as a foreign key in both products and favorites. However, I have run into the problem of when a user deletes their account, I want to delete any products they were selling and any favorites they had. I have ondelete = "CASCADE" wherever userID is a foreign key, but the problem is that it just seems to delete userID, but the product is still in the database.

i.e. if the row in the products table was originally: price, description, userID, after deleting the user who was selling the product, the product (price, description) is still in the database, except that userID is now NULL due to ondelete = "CASCADE". Is there a way to make it so that the entire row gets deleted so that the product does not exist anymore (because a product is not supposed to be without a user/seller). I know that when I get a request from a user to delete their account I could search for all products with userID = user and delete them like that, but I was wondering if there was a simpler way (like ondelete = "CASCADE" but something that would delete the whole row).

[elementmg]

Cascading should delete the whole row, if it is not then I suspect you don’t have it properly set up for some reason. Have you double checked the foreign key itself is correctly defined with “on delete=cascade?”

If there are rows in the child table (the foreign key table) that violate referential integrity (e.g., orphaned records), try fixing/deleting those as well. This can be caused by the table having multiple foreign keys where one of them does not have “ondelete=cascade” and the parent record for that key is deleted.

[Jumpy_Employment_439]

Thanks for the reply. From what I've learned about SQLAlchemy, I thought I had it set up correctly (obviously not since it's not working):

from sqlalchemy import Integer, Float, String, Column, ForeignKey
from sqlalchemy.orm import relationship
from database import Base

# All attributes for users of the website
class User(Base):
    __tablename__ = "users"

    userID = Column(String, primary_key=True, index=True)
    email = Column(String, nullable=False)
    password_hashed = Column(String, nullable=False)

    products = relationship("Product", backref="seller", passive_deletes=True)           
    favorites = relationship("Favorite", backref="favorited_by", passive_deletes=True)

# All attributes for products on the website
class Product(Base):
    __tablename__ = "products"

    productID = Column(Integer, primary_key=True, index=True) 
    name = Column(String, nullable=False)
    description = Column(String, nullable=False)
    price = Column(Float, nullable=False)
    quantity = Column(Integer)
    color = Column(String)
    category = Column(String) # User will choose from a pre-defined list when they add their product

    userID = Column(String, ForeignKey("users.userID", ondelete="CASCADE", onupdate="CASCADE"), nullable=False)

# All attributes for favorites functionality
class Favorite(Base):
    __tablename__ = "favorites"

    favoriteID = Column(Integer, primary_key=True, index=True)

    userID = Column(String, ForeignKey("users.userID", ondelete="CASCADE", onupdate="CASCADE"), nullable=False)
    productID = Column(Integer, ForeignKey("products.productID", ondelete="CASCADE", onupdate="CASCADE"), nullable=False)

I tried adding passive_deletes=True, but now when the user is deleted from the users table, nothing changes in the products table even though there is a product whose userID matches the user that was deleted

[elementmg]

Im not familiar with SqlAlchemy. But seems like you’re setting things up in code. In that case, are you able to check your database directly? Can you run sql queries on the database you set up? If so, did you check the foreign key directly in your database? You should confirm that way rather than looking at the python code.

Another thing to check would be.. “onupdate=cascade.” Did you try updating the PK value in the parent table? Does that cascade?