Deletion behavior with relationship

Question

This isn’t trully a problem, I just want to understand. Considering the following code:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, relationship
Base = declarative_base()

class AB(Base):
    __tablename__= 'ab'
    id_a = Column(Integer, ForeignKey('a.id', ondelete='CASCADE'), primary_key=True)
    id_b = Column(Integer, ForeignKey('b.id', ondelete='CASCADE'), primary_key=True)
    rel = Column(Unicode)

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    #1: doesn’t work try to set id_b to null
    rel_a = relationship('AB')
    # Works, but cascade='all' seems uneeded to me
    rel_a = relationship('AB', cascade='all')
    # Works 
    rel_a = relationship('AB', passive_deletes=True)

engine = create_engine('sqlite://', echo=True)

import logging
logger = logging.getLogger('sqlalchemy.engine.base.Engine')
logger.setLevel(logging.DEBUG)
handler = logger.handlers[0]
handler.setLevel(logging.DEBUG)
handler.setFormatter(logging.Formatter('%(levelname)s %(message)s', ''))

Base.metadata.create_all(engine)

sess = sessionmaker(engine)()

a1 = A()
b1 = B()
ab = AB()

sess.add_all([a1,b1])
sess.flush()

ab.id_a = a1.id
ab.id_b = b1.id
ab.rel = u'truite'
sess.add(ab)
sess.flush()
sess.delete(b1)
sess.flush()

I want records from AB table to be removed when related records from B are removed. I tried 3 types of relations (check in B table):

• 1: Doesn’t work (AssertionError: Dependency rule tried to blank-out primary key column 'ab.id_b' on instance ''), whereas if try to remove it directly in the database, constraints are correctly used, and records from AB are removed.

• 2: Works, I don’t get why this is needed because generated databases are identical (you can check the diff on output)

• 3: works, the DB constraints do the work.

Leaving (3) apart, I don’t get why (2) is needed, because the ondelete='cascade' is already set, and generated DB is identical. My guess would be with (1), SQLAlchemy has enough information to has the correct behavior.

Am I missing something? Thanks.

Answer 1

cascade on relationship configures the cascades of Session operations, such as Session.delete. It's independent from any ON X CASCADE directives you may have on your foreign keys constraints in the database itself.

In your case, having cascade='all' tells SQLAlchemy to cascade the Session.delete (among other operations) from the parent object (AB) to the child object. Without it, the default mode of operation is to put NULL into the foreign key column and let the referenced object be.

On the other hand, passive_deletes=True instructs SQLAlchemy to rely on database to cleanup deleted objects through the ON DELETE CASCADE directives. This prevents SQLAlchemy from issuing the DELETE query by itself, as it would do in the relationship(cascade=...) case.