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.

Deletion behavior with relationship

1 Answers