Just had a problem come up in a database that was previously working - at least I thought it was.  I'm using PHP as the client.

 

Table Nodes has ID.

    Table Routes has foreign key NODE_ID.

        Table Elevations has foreign key ROUTE_ID.

 

The foreign keys have cascade update & delete.

 

As part of an update process, I first delete all routes & elevations for a given node.  Theoretically, I could rely on the cascade delete, but (using a stored procedure) for a given Node I identify the associated routes, then explicitly delete all elevations for the routes, then delete the routes.  And for convenience, the proc returns the node ID.

 

I call this procedure with an explicit read/write transaction and then commit the transaction. Then I proceed with inserting the new values.

 

Just ran into an issue where Firebird via PHP was reporting foreign key errors.  I tried a few debug lines, then examined my logic.  In my PHP client, I start a transaction, perform the deletions, then do the insert/updates, then commit.  The theory being a full all-or-nothing update.  But since that now wasn't working...thought I'd try explicitly committing the delete first, then performing the updates.

 

Same issue.  Now I'm really confused.  So...break out FlameRobin.  I perform the steps manually...

    select NID from NODES - gives me an one.

        select RID from ROUTES where NODE_ID = NID - gives me routes.

            select EID from ELEVATIONS where ROUTE_ID = RID gives me a lot.

 

Ok...everything's there.  Now execute:

    select NID from purge_routes(1234)

 

Now commit in that window.  Then execute the route & elevation queries again, and I shouldn't see anything...but I do!  Close them and run again...now they're clear.  I haven't had this happen before - either I'm doing something stupid, or have I got some kind of corruption?