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.

Answer 2

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table.

It will reject any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.

SO Answers for Foreign Key Constraint OR Integrity Error

Update

    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;
      
    INSERT into ORDERS values(1,20.00);
    INSERT into ORDEREDITEMS values(1,1,1,2);

Now when you run above command in MySql your data is inserted successfully in Orders and ORDEREDITEMS DEMO1

So when does this error generates

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

It's generated when your child (ORDEREDITEM) has invalid key of Parent(i.e child contains invalid foreign key which is either deleted or does not exist from your Parent table)

If i try to do this

    INSERT into ORDEREDITEMS values(1,1,1,2);
    INSERT into ORDERS values(1,20.00);

I get error as OP got just by changing order of Insertion
even while deleting you can face this error

    INSERT into ORDERS values(1,20.00);

    DELETE FROM ORDERS
    WHERE ORDERSID=1;

    INSERT into ORDEREDITEMS values(1,1,1,2);