How to fix "UNIQUE constraint failed" from VACUUM (also INTEGRITY_CHECK fails)

Question

I use an app which creates this SQLite DB with this table:

CREATE TABLE expense_report (_id INTEGER PRIMARY KEY, ...)

And for some reason that _id (which is the ROWID) became invalid in that DB. When I scan the table I see that the last rows got an _id which was already being used long ago:

1,2,3...,1137,1138,...,1147,1149,...,12263,12264,1138,...,1148

I highlighted above the ranges in which I see that I have the same _id for completely different rows (the rest of the values do not match at all).

And querying this DB usually gets me inaccurate results due to that. For instance:

SELECT
    (SELECT MAX(_ID) FROM expense_report) DirectMax
    , (SELECT MAX(_ID) FROM (SELECT _ID FROM expense_report ORDER BY _ID DESC)) RealMax;

| DirectMax | RealMax |
| 1148      | 12264   |

And inserting a new row into this table via DB Browser for SQLite also generates an _id of 1149 (instead of 12265), so the problem becomes worse if I keep using this DB.

Running PRAGMA QUICK_CHECK or PRAGMA INTEGRITY_CHECK show this error response:

*** in database main ***
On page 1598 at right child: Rowid 12268 out of order

And running VACUUM also detects the problem but doesn't seem to be able to fix it:

Execution finished with errors.
Result: UNIQUE constraint failed: expense_report._id

Anyone knows a way to fix these duplicate ROWID values?

Answer 1

I initially fixed my problem with that DB just reverting to older backup of that DB.

But today I found that it happened again (to other records), and I finally fixed it via manual editions with DB Browser for SQLite.

I have this query to check the IDs of the repeated rows:

SELECT _id, COUNT(*) c FROM expense_report GROUP BY _id HAVING c > 1;

But this particular query was returning no rows initially. That changed after:

1. Removed the PK from the expense_report table.

2. Ran that query to check the IDs of the repeated rows again and now I got:

    1506    2
    1507    2
    1508    2
   

3. Browsed expense_report and searched for each of those keys, and found that they all have incorrect values in the amount column and they all began with 2022-12.

4. Searched for 2022-12 in the amount column.

5. Selected all the results (which are only those 3 bad rows) and deleted them via DEL key.

Then all the queries posted in the question worked properly.