I’ve made this request before, but now it’s becoming more urgent due to some fairly severe issues I’m experiencing.
The database tools right now really don’t do all that much, and don’t do anything about referential integrity. That means you can have Name records without People records, or citations or media linked to non-existent People.
RM doesn’t use built in SQLite referential integrity because the Owner isn’t always in the same table, as things like Media and Citations can link to different things. That’s fine, and it makes sense, but when data gets out of whack some means of fixing it, or at least identifying the problems, is in order.
So, for every table that incudes a link to another table, the tool should validate which links are valid and report those that are not. This report should be savable so you can work through it to correct issues. In addition truely orphaned records should be removed if they can’t be corrected be the UI.
For example, another user recently reported mismatches between the number of uses reported for a citation and the number of uses that actually exist for the same citation. The discrepancy turned out to be phantom records in RM’s CitationLinkTable which link to records such as person records or fact records that no longer exist.
It’s hard to picture how such phantom records come to exist. For example, if you delete a person or a fact, then any citation links to that person or fact should also be deleted at the same time. The citation itself should not be deleted - just the links to items that no longer exist.
I found a very few such phantom citation links in my own database and I created a very short and simple SQlite script to delete them. I can’t imagine any universe in which it would be possible to fix such links instead of just deleting them. I therefore have initiated a long term project to find and delete any such phantom links. Finding and deleting such phantom links is not actually very hard.
As you described, such phantom links represent referential integrity problems. It seems to me that RM’s existing Clean Phantom Records tool should delete all such phantom records in all tables. As I already mentioned, I can’t seen any way to fix any such records. I think that only remedy is to delete them.
There was a time when there was no Delete Phantoms tool. I published a sqlite script with that capability back in the RM4 era and, eventually, a less comprehensive tool was added. What also happens is some feature is added that opens up the risk of new phantom or orphaned records and nothing is done to address it in the existing tool. So new features attract new customers who may get trapped or confused by their existence and the only advice from Support is the blunt instrument drag’n’drop everyone to a new database which has its unwanted side effects.