Cascade Operations & Referential Integrity in SQLAlchemy

Cascade operations automatically propagate changes (insert, update, delete) from parent objects to related children. Referential integrity constraints ensure database consistency by preventing orphaned records and invalid references. Together, cascades and constraints form the safety net of a relational database.

Understanding Cascade Operations

Cascade rules define what happens to child records when the parent is modified or deleted. SQLAlchemy supports several cascade options that apply to relationships:

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

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    
    # Define cascade behavior for child addresses
    addresses = relationship(
        "Address",
        back_populates="user",
        cascade="all, delete-orphan"  # Cascade options
    )

class Address(Base):
    __tablename__ = "addresses"
    
    id = Column(Integer, primary_key=True)
    street = Column(String(200), nullable=False)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    
    user = relationship("User", back_populates="addresses")

Cascade is defined on the parent side of the relationship. The string "all, delete-orphan" means:

• all: Include save-update, merge, refresh-expire, expunge behavior
• delete-orphan: Delete children if they're no longer referenced

Cascade Options Explained

Option	Behavior
save-update	Add new related objects to session when adding parent (default)
delete	Delete children when parent is deleted
delete-orphan	Delete children if they lose all parent references
merge	Merge child objects when merging parent
refresh-expire	Refresh child objects when refreshing parent
expunge	Remove child from session when removing parent
all	Includes save-update, merge, refresh-expire, and expunge

Use cascade="all, delete-orphan" for owned relationships (user owns addresses). Use cascade="all, delete" when deleting a parent should clean up children. Use no cascade (default) for loosely coupled relationships.

Examples: When to Use Cascades

Cascade Delete: Owned Relationships

When a user is deleted, their addresses should be deleted too (the user owns the addresses):

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), nullable=False)
    
    addresses = relationship(
        "Address",
        back_populates="user",
        cascade="all, delete-orphan"
    )

# Usage
with Session(engine) as session:
    user = session.query(User).filter_by(id=1).first()
    session.delete(user)
    session.commit()
    # Both user and all their addresses are deleted

Cascade Save-Update: Automatic Session Management

By default, adding a parent automatically adds related children to the session:

class Order(Base):
    __tablename__ = "orders"
    
    id = Column(Integer, primary_key=True)
    order_date = Column(DateTime, default=datetime.utcnow)
    
    items = relationship(
        "OrderItem",
        back_populates="order",
        cascade="all, delete-orphan"  # Includes save-update by default
    )

# Usage
with Session(engine) as session:
    order = Order()
    item1 = OrderItem(product="Widget", quantity=5)
    item2 = OrderItem(product="Gadget", quantity=3)
    
    order.items = [item1, item2]
    session.add(order)
    # item1 and item2 are automatically added to the session
    session.commit()
    # All objects are inserted in one transaction

No Cascade: Loose Associations

When books belong to a library but can be reassigned, don't use cascade:

class Book(Base):
    __tablename__ = "books"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    library_id = Column(Integer, ForeignKey("libraries.id"))
    
    # No cascade—deleting a library doesn't delete its books
    library = relationship("Library", back_populates="books")

# Usage
with Session(engine) as session:
    library = session.query(Library).filter_by(id=1).first()
    session.delete(library)
    session.commit()
    # Library is deleted; books remain (with library_id = NULL if nullable)

Referential Integrity Constraints

Referential integrity is enforced at the database level via foreign key constraints. A foreign key ensures that every reference points to a valid record:

from sqlalchemy import ForeignKey

class Book(Base):
    __tablename__ = "books"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    
    # Foreign key: author_id MUST reference an existing authors.id
    author_id = Column(Integer, ForeignKey("authors.id"), nullable=False)

# Usage
with Session(engine) as session:
    book = Book(title="New Novel", author_id=999)  # Invalid author ID
    session.add(book)
    session.commit()  # Raises IntegrityError—no author with id=999

SQLAlchemy lets you define cascading deletes and updates at the database level:

author_id = Column(
    Integer,
    ForeignKey("authors.id", ondelete="CASCADE", onupdate="CASCADE"),
    nullable=False
)

The ondelete="CASCADE" tells the database to delete child records when the parent is deleted. The onupdate="CASCADE" updates child foreign keys if the parent's primary key changes.

Cascade vs. Database Constraints

SQLAlchemy cascades operate at the ORM level (Python). Database constraints operate at the database level (SQL). Use both for a complete safety net:

Scenario	SQLAlchemy Cascade	Database Constraint
Delete parent	cascade="delete"	ondelete="CASCADE"
Add child without parent	Prevented by ForeignKey	Rejected by database
Update parent ID	cascade="save"	onupdate="CASCADE"

SQLAlchemy cascades are checked when you call .commit(). Database constraints are checked by the database itself, protecting against direct SQL modifications.

Cascade with Many-to-Many Relationships

Many-to-many relationships usually don't need cascades (deleting one author shouldn't delete books):

from sqlalchemy import Table

book_author = Table(
    "book_author",
    Base.metadata,
    Column("book_id", Integer, ForeignKey("books.id")),
    Column("author_id", Integer, ForeignKey("authors.id")),
)

class Book(Base):
    __tablename__ = "books"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    
    # No cascade—deleting an author doesn't delete books
    authors = relationship(
        "Author",
        secondary=book_author,
        back_populates="books"
    )

With association objects, use careful cascades:

class BookAuthor(Base):
    __tablename__ = "book_author"
    
    book_id = Column(Integer, ForeignKey("books.id"), primary_key=True)
    author_id = Column(Integer, ForeignKey("authors.id"), primary_key=True)
    
    # Cascade deletes when either book or author is deleted
    book = relationship("Book", cascade="all, delete")
    author = relationship("Author", cascade="all, delete")

Troubleshooting Cascade Issues

Issue: Children Not Deleted When Parent Deleted

Problem: You delete a parent, but children remain in the database.

Solution: Add cascade="all, delete" or cascade="all, delete-orphan" to the relationship. Or set ondelete="CASCADE" on the foreign key.

Issue: Orphaned Records After Removing from Relationship

Problem: You remove a child from a parent's relationship, but it's not deleted.

Solution: Use cascade="delete-orphan" on the relationship. This deletes children that lose all parent references.

Issue: IntegrityError on Insert

Problem: You try to insert a record with an invalid foreign key.

Solution: Ensure the parent record exists and is committed before inserting the child. Or use cascade to automatically add parents to the session.

Key Takeaways

• Cascades are ORM-level rules that propagate changes to children (save, delete, merge, expunge).
• cascade="all, delete-orphan" for owned relationships (user owns addresses).
• cascade="all, delete" when deleting a parent should delete children.
• No cascade (default) for loose associations (books belong to many libraries).
• Database constraints (ondelete="CASCADE") enforce referential integrity at the SQL level.
• Use both SQLAlchemy cascades and database constraints for complete safety.

Frequently Asked Questions

What's the difference between delete and delete-orphan?

delete deletes children when the parent is deleted. delete-orphan deletes children if they lose all parent references (stronger—useful for owned relationships).

Should I use SQLAlchemy cascades or database constraints?

Use both. SQLAlchemy cascades protect your ORM; database constraints protect against direct SQL. A defense-in-depth approach prevents bugs.

If I set cascade at the ORM level, do I still need database constraints?

Yes. If someone modifies the database directly (via SQL or another tool), database constraints ensure consistency. SQLAlchemy cascades are bypassed.

Can I set different cascades on different relationships?

Yes. Each relationship can have its own cascade rules. This is common in complex schemas.

What happens if I delete a parent without setting cascade?

If no cascade is set, SQLAlchemy checks the foreign key constraint. If the constraint is NOT NULL, deletion fails. If nullable, the foreign key is set to NULL (if ondelete="SET NULL").

Further Reading

• SQLAlchemy Cascade Options
• Foreign Key Constraints
• Relationship Configuration
• Database-Level Referential Integrity