Cascading a delete to a many-to-many association table?

Question

I have a problem with cascading a delete. I have two tables, and they are mapped many-to-many:

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) 

This is the error I get:

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". 

Has anybody got an idea what I'm doing wrong?

I also asked the question on the sqlalchemy mailing list, and got the right answer:

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.

session.mapper(File, file_table, properties={ 
    'host': relation(FileHost, 
                     backref=backref('files', 
                                     cascade='all,delete-orphan', 
                                     single_parent=True), 
                     secondary=file_hosted, 
                     uselist=False) 
}) 

Answer 1

It should be noted that while this wasn't the problem in this particular case, you may still get this error if you don't include ondelete="CASCADE" in the declaration of your foreign keys. Even after declaring cascade="all,delete" in my relationships, I was still getting this error until I added the ondelete attribute. You may want to add onupdate="CASCADE" as well.