title: Many-to-Many with association object and all relationships defined crashes on delete
body: <p>When having a fully fledged many-to-many with all relations described, the deletion of one of the two main objects crashes. </p>
  
  <h2>Description</h2>
  
  <p><strong>Car</strong> (<code>.car_ownerships</code>)  &lt;-> (<code>.car</code>) <strong>CarOwnership</strong> (<code>.person</code>) &lt;-> (<code>.car_ownerships</code>) <strong>Person</strong></p>
  
  <p><strong>Car</strong> (<code>.people</code>) &lt;-----------------> (<code>.cars</code>) <strong>Person</strong></p>
  
  <h2>Problem</h2>
  
  <p>When deleting a <strong>Car</strong> or a <strong>Person</strong>
  SA first deletes the association object <strong>CarOwnership</strong> (because of the 'through' relationship with the <code>secondary</code> argument) and then tries to update the foreign keys to NULL in the same association objects, hence crashing.</p>
  
  <p>How should I solve this? I'm a little perplexed to see that this is not addressed in the docs nor anywhere I could find online, since I thought that this pattern was quite common :-/. What am I missing?</p>
  
  <p>I know I could have the <code>passive_deletes</code> switch on for the through relationship, but I'd like to keep the delete statement, just to prevent the update from happening or (make it happen before).</p>
  
  <p><strong>Edit</strong>: Actually, <code>passive_deletes</code> doesn't solve the problem if the dependent objects are loaded in session, as <code>DELETE</code> statement will still be issued. A solution is to use <code>viewonly=True</code>, but then I lose not only deletion but automatic creation of association objects. Also I find <code>viewonly=True</code> to be quite dangerous, because it lets you <code>append()</code> without persisting!</p>
  
  <h2>REPEX</h2>
  
  <h3>Setup</h3>
  
  
  
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy import create_engine, Table, Column, Integer, String, ForeignKey
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import relationship, backref, sessionmaker
  
  engine = create_engine('sqlite:///:memory:', echo = False)
  Base = declarative_base()
  Session = sessionmaker(bind=engine)
  session = Session()
  
  
  class Person(Base):
      __tablename__ = 'persons'
  
      id = Column(Integer(), primary_key=True)
      name = Column(String(255))
  
      cars = relationship('Car', secondary='car_ownerships', backref='people')
  
      def __repr__(self):
          return '&lt;Person {} [{}]&gt;'.format(self.name, self.id)
  
  class Car(Base):
      __tablename__ = 'cars'
  
      id = Column(Integer(), primary_key=True)
      name = Column(String(255))
  
      def __repr__(self):
          return '&lt;Car {} [{}]&gt;'.format(self.name, self.id)
  
  
  class CarOwnership(Base):
      __tablename__ = 'car_ownerships'
  
      id = Column(Integer(), primary_key=True)
      type = Column(String(255))
      car_id = Column(Integer(), ForeignKey(Car.id))
      car = relationship('Car', backref='car_ownerships')
      person_id = Column(Integer(), ForeignKey(Person.id))
      person = relationship('Person', backref='car_ownerships')
  
      def __repr__(self):
          return 'Ownership [{}]: {} &lt;&lt;-{}-&gt;&gt; {}'.format(self.id, self.car, self.type, self.person)
  
  Base.metadata.create_all(engine)
  </code></pre>
  
  <h3>Filing objects</h3>
  
  <pre class="lang-py prettyprint-override"><code>antoine = Person(name='Antoine')
  rob = Person(name='Rob')
  car1 = Car(name="Honda Civic")
  car2 = Car(name='Renault Espace')
  
  CarOwnership(person=antoine, car=car1, type = "secondary")
  CarOwnership(person=antoine, car=car2, type = "primary")
  CarOwnership(person=rob, car=car1, type = "primary")
  
  session.add(antoine)
  session.commit()
  
  session.query(CarOwnership).all()
  </code></pre>
  
  <h3>Deleting -> Crashing</h3>
  
  <pre class="lang-py prettyprint-override"><code>print('#### DELETING')
  session.delete(car1)
  print('#### COMMITING')
  session.commit()
  
  
  # StaleDataError                            Traceback (most recent call last)
  # &lt;ipython-input-6-80498b2f20a3&gt; in &lt;module&gt;()
  #       1 session.delete(car1)
  # ----&gt; 2 session.commit()
  # ...
  </code></pre>
  
  <h3>Diagnostics</h3>
  
  <p>The explanation I propose above is backed by the SQL statements given by the engine with <code>echo=True</code>:</p>
  
  <pre class="lang-py prettyprint-override"><code>#### DELETING
  #### COMMITING
  2016-07-07 16:55:28,893 INFO sqlalchemy.engine.base.Engine SELECT persons.id AS persons_id, persons.name AS persons_name 
  FROM persons, car_ownerships 
  WHERE ? = car_ownerships.car_id AND persons.id = car_ownerships.person_id
  2016-07-07 16:55:28,894 INFO sqlalchemy.engine.base.Engine (1,)
  2016-07-07 16:55:28,895 INFO sqlalchemy.engine.base.Engine SELECT car_ownerships.id AS car_ownerships_id, car_ownerships.type AS car_ownerships_type, car_ownerships.car_id AS car_ownerships_car_id, car_ownerships.person_id AS car_ownerships_person_id 
  FROM car_ownerships 
  WHERE ? = car_ownerships.car_id
  2016-07-07 16:55:28,896 INFO sqlalchemy.engine.base.Engine (1,)
  2016-07-07 16:55:28,898 INFO sqlalchemy.engine.base.Engine DELETE FROM car_ownerships WHERE car_ownerships.car_id = ? AND car_ownerships.person_id = ?
  2016-07-07 16:55:28,898 INFO sqlalchemy.engine.base.Engine ((1, 1), (1, 2))
  2016-07-07 16:55:28,900 INFO sqlalchemy.engine.base.Engine UPDATE car_ownerships SET car_id=? WHERE car_ownerships.id = ?
  2016-07-07 16:55:28,900 INFO sqlalchemy.engine.base.Engine ((None, 1), (None, 2))
  2016-07-07 16:55:28,901 INFO sqlalchemy.engine.base.Engine ROLLBACK
  </code></pre>
  
  <h1>EDITS</h1>
  
  <h2>Using <code>association_proxy</code></h2>
  
  <p>We can use association proxies to try and materialize the 'through' relationship. </p>
  
  <p>Nevertheless, in order to <code>.append()</code> a dependent object directly, we need to create a constructor for the association object. This constructor must be 'hacked' to be made bi-directional, so we can use both assignments:</p>
  
  <pre class="lang-py prettyprint-override"><code>my_car.people.append(Person(name='my_son'))
  my_husband.cars.append(Car(name='new_shiny_car'))
  </code></pre>
  
  <p>The resulting (midly tested) code is below, but I don't feel quite comfortable with it (what else is going to break because of this hacky constructor?).</p>
  
  <p><strong>EDIT:</strong> The way to go with association proxies is presented in RazerM's answer below. <code>association_proxy()</code> has a creator argument that alleviates the need for the monstrous constructor I ended up using below.</p>
  
  <pre class="lang-py prettyprint-override"><code>class Person(Base):
      __tablename__ = 'persons'
  
      id = Column(Integer(), primary_key=True)
      name = Column(String(255))
  
      cars = association_proxy('car_ownerships', 'car')
  
      def __repr__(self):
          return '&lt;Person {} [{}]&gt;'.format(self.name, self.id)
  
  class Car(Base):
      __tablename__ = 'cars'
  
      id = Column(Integer(), primary_key=True)
      name = Column(String(255))
  
      people = association_proxy('car_ownerships', 'person')
  
      def __repr__(self):
          return '&lt;Car {} [{}]&gt;'.format(self.name, self.id)
  
  
  class CarOwnership(Base):
      __tablename__ = 'car_ownerships'
  
      id = Column(Integer(), primary_key=True)
      type = Column(String(255))
      car_id = Column(Integer(), ForeignKey(Car.id))
      car = relationship('Car', backref='car_ownerships')
      person_id = Column(Integer(), ForeignKey(Person.id))
      person = relationship('Person', backref='car_ownerships')
  
      def __init__(self, car=None, person=None, type='secondary'):
          if isinstance(car, Person):
              car, person = person, car
          self.car = car
          self.person = person
          self.type = type        
  
      def __repr__(self):
          return 'Ownership [{}]: {} &lt;&lt;-{}-&gt;&gt; {}'.format(self.id, self.car, self.type, self.person)
  </code></pre>