Delete behavior with relation

Question

This is not a problem, I just want to understand. Given 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 the records from table AB deleted when deleting related records from B I tried 3 types of relationships (check table B):

• 1: Does not work (AssertionError: Dependency rule tried to exclude an empty primary key column 'ab.id_b' per instance ''), whereas if you try to delete it directly in the database, the restrictions will be used correctly and the records from AB will be deleted.

• 2: It works, I don’t understand why this is necessary, because the generated databases are identical (you can check the diff in the output)

• 3: works, DB constraints do the work.

Leaving (3) separately, I don’t understand why (2) is necessary, because ondelete='cascade' already installed, and the generated database is identical. My guess would be with (1), SQLAlchemy has enough information to have the correct behavior.

Am I missing something? Thanks.

Answer 1

cascade on relationship sets up cascades of Session operations, such as Session.delete . It does not depend on any ON X CASCADE directives, which may have restrictions on foreign keys in the database itself.

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

passive_deletes=True , on the other hand, tells SQLAlchemy to rely on the database to clean up deleted objects using ON DELETE CASCADE directives. This prevents SQLAlchemy from querying DELETE , as would be the case with relationship(cascade=...) .