cascading a delete in a many-to-many relation

[tom]

Hi,

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


Can somebody please tell me what I'm doing wrong because I tried to
find an answer and couldn't. This was the only somewhat related thing
I found: http://www.mail-archive.com/sqlal...@googlegroups.com/msg13198.html

--tom

[Conor]

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 use_list=False.

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

-Conor

[tom]

That was indeed my problem, thank you very much! But I still cannot
wrap my brain around cascading, if anyone has a link to a good writeup
I'd be glad.

--tom