CitationTable Errors, Loss of Referential Integrity

#1 · 2020-11-29, 11:26 am

Quote from thejerrybryan on 2020-11-29, 11:26 am
I have made a distressing discovery about my production RM database. Out of over 300,000 entries in the CitationTable, a bit more than 2,000 of the entries have lost referential integrity. That means that a citation exists and is joined to a primary key that doesn't exist in another table. It's hard to know how this came about, but some of the citations that are orphaned are obviously very old and haven't been touched in many years. Therefore, whatever the problem is, it appears that it has been going on for many years.
The good news is that it appears that none of the orphaned citations seem to be of any significance whatsoever and can be safely deleted. Because I am a source splitter, I can tell in great detail what a citation is for based on its Master Source. And it's therefore pretty easy to tell most of the time all the items to which a citation should be linked. I haven't yet found any items that are missing any of my orphaned citations.
All the orphaned citations are to facts - to items in the EventTable. None of my citations to people, families, or names are orphaned.
I was planning to run an SQLite script to delete the orphaned citations. But the other good news is that there is a way to delete them without running a script at all. Actually, there are two ways to delete the orphaned citations without running a script. One way is to do a drag and drop of my whole RM database to a new and empty database. I'm always loathe to use drag and drop in this fashion because it risks losing data. But I discovered a second way to delete the orphaned citations without running an SQLite script is simply to do a File>Copy from within RM's user interface.
I would have guessed that RM's File>Copy would have simply done sort of a Windows level copy file operation. But it appears instead that RM's File>Copy is doing a table level copy with SQLite, copying each table in turn. And it appears to be doing so in a way that restores referential integrity by deleting rows with foreign keys that do not match any primary key. So RM's File>Copy is the approach I have taken to solving this problem.
I have uploaded four queries. Each query looks for orphaned citations, one query for citations to people, one query for citations to families, one query for citations to facts, and one query for citations to names. These are quick and dirty citations with essentially no documentation included. Each of them includes an AND E.EventID IS NULL clause which is commented out (or P.PersonID or F.FamilyID or N.NameID, as appropriate). You can uncomment out the clause to see just the orphaned citations. But I prefer to run the queries with the NULL clause commented out so that I can see that the query is really working.
I would be most curious to see if anybody else's production database that has been around for a long time has this problem or not.
Jerry

I have made a distressing discovery about my production RM database. Out of over 300,000 entries in the CitationTable, a bit more than 2,000 of the entries have lost referential integrity. That means that a citation exists and is joined to a primary key that doesn't exist in another table. It's hard to know how this came about, but some of the citations that are orphaned are obviously very old and haven't been touched in many years. Therefore, whatever the problem is, it appears that it has been going on for many years.

The good news is that it appears that none of the orphaned citations seem to be of any significance whatsoever and can be safely deleted. Because I am a source splitter, I can tell in great detail what a citation is for based on its Master Source. And it's therefore pretty easy to tell most of the time all the items to which a citation should be linked. I haven't yet found any items that are missing any of my orphaned citations.

All the orphaned citations are to facts - to items in the EventTable. None of my citations to people, families, or names are orphaned.

I was planning to run an SQLite script to delete the orphaned citations. But the other good news is that there is a way to delete them without running a script at all. Actually, there are two ways to delete the orphaned citations without running a script. One way is to do a drag and drop of my whole RM database to a new and empty database. I'm always loathe to use drag and drop in this fashion because it risks losing data. But I discovered a second way to delete the orphaned citations without running an SQLite script is simply to do a File>Copy from within RM's user interface.

I would have guessed that RM's File>Copy would have simply done sort of a Windows level copy file operation. But it appears instead that RM's File>Copy is doing a table level copy with SQLite, copying each table in turn. And it appears to be doing so in a way that restores referential integrity by deleting rows with foreign keys that do not match any primary key. So RM's File>Copy is the approach I have taken to solving this problem.

I have uploaded four queries. Each query looks for orphaned citations, one query for citations to people, one query for citations to families, one query for citations to facts, and one query for citations to names. These are quick and dirty citations with essentially no documentation included. Each of them includes an AND E.EventID IS NULL clause which is commented out (or P.PersonID or F.FamilyID or N.NameID, as appropriate). You can uncomment out the clause to see just the orphaned citations. But I prefer to run the queries with the NULL clause commented out so that I can see that the query is really working.

I would be most curious to see if anybody else's production database that has been around for a long time has this problem or not.

Jerry

Uploaded files:

#2 · 2020-11-29, 1:33 pm

I hate when this happens, but I just now repeated all my steps and now the File>Copy does not delete the citations with referential integrity problems. So it looks like I'm going to have to delete the invalid CitationTable entries with a script. I have no explanation why the first time I tried the File>Copy it seemed to work and now it doesn't. There must have been some sort of user error in my process.

The original question does remain of how the invalid CitationTable entries came into being in the first place.

Jerry

#3 · 2020-11-29, 4:03 pm

Jerry,

My database is very old. I refreshed my knowledge on how to run queries, not having done so for many years. I'm still in the beginners stage.

Using SQLite Expert Personal 5.3 (x64) on Windows 10 and after running your 4 queries, I had only 3 instances of null. Are these orphaned citations?

Null results after running Events sql
C_ID Source E_ID C_Type F_Type
62633 (null) 59503 2 Marriage
62650 (null) 59584 2 Marriage

Null results after running People sql
Source RIN C_Type
69758 (null) 4890 0

#4 · 2020-11-29, 4:34 pm

Yes, that's correct. My interpretation is that those are orphaned citations. I'm not sure if I'm glad or sad that I'm not the only one with the orphaned citations. It's interesting that you had orphans both for people and for events. I had them only for events.

Since File>Copy didn't really get rid of my orphans like I originally thought, I ran a DELETE script to take care of the problem. So far, so good with my database after running the DELETE script.

Jerry

#5 · 2020-11-29, 5:25 pm

These orphaned records have been an issue since RM4 and probably pre-dates RM's conversion to SQLite3 at that version. I have posted pages and scripts to this site and its predecessor since 2010, according to pages returned by the #phantom tag. These phantoms are/were a consequence of the RM program not bothering to clean them out after merging or deleting persons and maybe other actions.

One of my earliest efforts was a comprehensive query expanding on RM's File > Database Properties view; see

RMGC_Properties – Query #databaseproperties #subqueries #integrity

It addressed many things unreported by RM, including what you have been describing. It was integrated in my now ancient application.

Tom

#6 · 2020-11-29, 5:29 pm

What is the delete script that you ran?

#7 · 2020-11-29, 8:27 pm

I have attached my delete script. It's only for phantom citations that are linked to facts (RM's EventTable) because those are the only phantoms I had. Delete scripts for phantom citations for people, families, and names would follow the same model.

But in the mean time, it is my intention to chase down the queries referenced by Tom. I am confident they are much more comprehensive than my little quick and dirty queries I have posted in this thread.

Jerry

Uploaded files:

delete_null_citation_links_events.sql