SQLAlchemy Delete Cascade Integrity Error

Question

I am trying to delete an order from my database while deleting all ordereditems that relate to it.

with contextlib.closing(DBSession()) as session:
    try:
        returnedOrder = session.query(ORDER).filter_by(ORDERSID=orderID).first()
        session.delete(returnedOrder)
        session.commit()
    except exc.SQLAlchemyError, error:
        session.rollback()
        raise_database_error(error)
    else:
        return '1'

Here are the related classes (some items have been removed):

class ORDER(Base):
    __tablename__ = 'ORDERS'

    ORDERSID = Column(Integer, primary_key=True)
    ORDERSCOST = Column(Numeric(19, 4), nullable=False)

    ORDEREDITEM = relationship("ORDEREDITEM")  


class ORDEREDITEM(Base):
    __tablename__ = 'ORDEREDITEMS'
    __table_args__ = (
        Index('AK_ORDERSID_ITEMID', 'ORDERSID', 'ITEMSID', unique=True),
    )

    ORDEREDITEMSID = Column(Integer, primary_key=True)
    ITEMSID = Column(ForeignKey(u'ITEMS.ITEMSID'), nullable=False, index=True)
    ORDERSID = Column(ForeignKey(u'ORDERS.ORDERSID', ondelete=u'CASCADE'), nullable=False)
    ORDEREDITEMSQUANTITY = Column(Integer, nullable=False)

    ORDER = relationship(u'ORDER')

The SQL file:

create table ORDERS
(
 ORDERSID             int not null auto_increment,     
 ORDERSCOST           decimal(19,4) not null,
 primary key (ORDERSID)
);

create table ORDEREDITEMS
(
 ORDEREDITEMSID       int not null auto_increment,
 ORDERSID             int not null,
 ITEMSID              int not null,
 ORDEREDITEMSQUANTITY int not null,
 primary key (ORDEREDITEMSID),
 unique key AK_ORDERSID_ITEMID (ORDERSID, ITEMSID)
);

alter table ORDEREDITEMS add constraint FK_ORDER_ORDEREDITEM foreign key (ORDERSID)
  references ORDERS (ORDERSID) on delete CASCADE on update restrict;

When I run this, I get the error:

 (IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`my_database`.`ordereditems`,

 CONSTRAINT `FK_ORDER_ORDEREDITEM` FOREIGN KEY (`ORDERSID`) REFERENCES `ORDERS` (`ORDERSID`) ON DELETE CASCADE)') 

'UPDATE `ORDEREDITEMS` SET `ORDERSID`=%s WHERE `ORDEREDITEMS`.`ORDEREDITEMSID` = %s' (None, 3L)

When I perform the same operation directly on phpMyAdmin, it works as expected.

Answer 1

A fully working example is usually most helpful, but I was able to filling in the missing parts from your example, and reproduce your exact problem-- the UPDATE statement that is attempted when trying to commit after delete. (Fully working code here, all pure python, no SQL statements.)

This question has been asked before many times, and I found the answer in this SO question.

The fix is simply to change the ORDEREDITEM relationship from

ORDEREDITEM = relationship("ORDEREDITEM")

to

ORDEREDITEM = relationship("ORDEREDITEM", cascade="all,delete")

See the sqlalchemy docs here.