While doing further development work today on the customer service module for our Grails-based web site administration tool, I encountered hibernate object retrieval failure exceptions when trying to access the Show views for some of our users. The console indicated these exceptions are due to rows referencing non-existent identifiers, or foreign keys.
A look into the actual database revealed that some of our user-affiliated tables still reference broken hard-deleted parent records, hard-deleted by someone other than me,
because I don’t believe in hard-deleting records.
Evidently, my previous attempt to fix all of these broken records was not 100 percent successful. We still have 177 broken records. Not bad, but still a problem.
A brief search online provided me with the following query useful for identifying records that reference a nonexistent parent record:
SELECT * FROM table1
WHERE table2id NOT IN (select `id` FROM table2)
ORDER BY table2id;
I’ve now added this query to my list of regular data integrity checks.