On Jul 25, 2011, at 9:47 AM, Aviv Giladi wrote:
I can't seem to make cascade deletes work in sqlalchemy.
I have a parent class (called Rating), a sub class (Subrating) and a
third class called SubRatingProperty.
There is a one-to-one relationship between Rating and SubRating - each
Rating can only have one specific SubRating object. Next, the
SubRatingProperty refers to a row in a table with fixed values. There
are 3 SubRatingProperty entries - property1, property2 and property3.
The SubRating class can have one or more of either property1,
property2 and property3, therefore the relationship is many-to-many (a
SubRatingProperty can have more than one properties, and for example
property1 can be assigned to more than one SubRatingProperty's).
Here is the code that defines all of this:
subrating_subratingproperty_association =
Table('subrating_subratingproperty_association', Base.metadata,
Column('subrating_id', Integer,
ForeignKey('subratings.id')),
Column('subrating_property_id',
Integer, ForeignKey('subrating_properties.id')))
class SubRatingProperty(Base):
__tablename__ = 'subrating_properties'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subratings = relationship(SubRating,
secondary=subrating_subratingproperty_association,
backref=subrating_properties)
class SubRating(Base):
__tablename__ = 'subratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating_id = Column(Integer, ForeignKey('subratings.id'))
subrating = relationship(SubRating, backref=backref(rating,
uselist=False))
Everything works fine, but I can't figure out how to do cascade
deletes. I am deleting Rating objects, and when I do, I would like the
according SubRating object to be deleted, as well as all the entries
in the association table. So deleting Rating1 would delete its
SubRating, as well as all the connection between the SubRating and
SubRatingProperty's.
I have tried adding cascade=all to the relationship call,
you have two relationships() here to build the full chain so you'd need
cascade='all, delete-orphan' on both Rating.subrating as well as
SubRating.subrating_properties (use the backref() function instead of a string
to establish the cascade rule on that end.
and I also
tried adding ondelete=cascade) to the ForeignKey call.
if all the involved FOREIGN KEYs are generated with ON DELETE CASCADE as this
would accomplish, as long as you are not on SQLIte or MySQL MyISAM the deletes
will be unconditional.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
