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?