title: Cascading a delete to a many-to-many association table?
body: <p>I have a problem with cascading a delete. I have two tables, and they 
  are mapped many-to-many: </p>
  
  <pre><code>class File(object): pass 
  file_table = Table('file', metadata, 
          Column('id', Integer, primary_key=True, autoincrement=True), 
          Column('filename', String(255)), 
  } 
  
  class FileHost(object): pass 
  file_host = Table('host', metadata, 
          Column('id', Integer, primary_key=True, autoincrement=True ), 
          Column('name', String(255)), 
  ) 
  
  file_hosted = Table('file_hosted', metadata, 
          Column('id_host', Integer, ForeignKey('host.id')), 
          Column('id_file', Integer, ForeignKey('file.id')) 
  ) 
  
  session.mapper(File, file_table, properties={ 
      'host': relation(FileHost, secondary=file_hosted, backref='files', 
                          cascade='all,delete-orphan', single_parent=True) 
  }) 
  session.mapper(FileHost, file_host) 
  </code></pre>
  
  <p>This is the error I get:   </p>
  
  <pre><code>sqlalchemy.exc.IntegrityError: 
  (IntegrityError) update or delete on table "file" violates
  foreign key constraint "file_hosted_id_file_fkey" on table "file_hosted" 
  DETAIL:  Key (id)=(50905) is still referenced from table "file_hosted". 
  </code></pre>
  
  <p>Has anybody got an idea what I'm doing wrong?  </p>
  
  <p>I also asked the question <a href="http://groups.google.com/group/sqlalchemy/browse_thread/thread/f73aa1608786ce30" rel="noreferrer">on the sqlalchemy mailing list</a>, and got the right answer:</p>
  
  <blockquote>
    <p>You are telling SQLAlchemy to cascade File deletes to FileHost, but you 
    want it the other way around. You can fix this by moving the 
    cascade='all,delete-orphan' and single_parent=True clauses into the 
    backref. You also probably want uselist=False. </p>
  </blockquote>
  
  <pre><code>session.mapper(File, file_table, properties={ 
      'host': relation(FileHost, 
                       backref=backref('files', 
                                       cascade='all,delete-orphan', 
                                       single_parent=True), 
                       secondary=file_hosted, 
                       uselist=False) 
  }) 
  </code></pre>