CODEWITHSUNDEEP
pythonsql-serversqlalchemycascade
push 22
push 22

Reputation: 1182

python sqlalchemy - can't get cascade delete to work - in mssql manager

I have this code, thats been mostly taken from the sqlalchemy site

class Order(Base):
    __tablename__ = 'order'
    id = Column(Integer, Sequence('tri_id_seq'), primary_key=True)
    text = Column(String(1024), nullable=False)
    items = relationship("Item", cascade="save-update, delete-orphan, merge, delete", backref="parent")

class Item(Base):
    __tablename__ = 'item'
    id = Column(Integer, Sequence('tri_id_seq'), primary_key=True)
    text = Column(String(1024), nullable=False)
    parent_id = Column(Integer, ForeignKey('order.id'))

I want deletes to Order to cascade down and delete its items as well. In code:

# test insert/delete - save data to mssql server
i1 = Item(text="item one")
i2 = Item(text="item two")
o = Order(text="one", items=[i1, i2])
session.add(o)
session.commit()

session.delete(o) # delete it
# tests to make sure items/order gone ....
session.commit()

This works ok.

BUT if I try and delete an Order in MS SQL management studio. i.e.

DELETE FROM [dbo].[order] WHERE id = 1

I get the error "the DELETE statement conflicted with the REFERENCE constraint FK__item__parent_id_1D00044F" The conflict error blah blah....

I guess theres something missing on the relationship definitions but I can't see it.

Any help/thoughts?

ta.

Upvotes: 3

Views: 2996

Answers (1)

Rusty
Rusty

Reputation: 914

class Comment(Base):
    __tablename__ = 'comments'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete='CASCADE'), nullable=False)

    user = relationship("User", backref=backref('comments', cascade="all,delete", order_by=id))

This kind of setup works for me, my User class doesn't have any special fields except for primary key. So basically, this works as intended, when I delete the user - his comments are gone as well. It doesn't have to be deletion through SQLAlchemy either, this code creates an appropriate table structure, that even if you delete user manually (sql query) - cascade deletion will still work.

Upvotes: 2

Related Questions