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?

score 0 · Answer 1 · answered Sep 02 '23 at 01:19

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:

Removed the PK from the expense_report table.
Ran that query to check the IDs of the repeated rows again and now I got:
```
 1506    2
 1507    2
 1508    2
```
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.
Searched for 2022-12 in the amount column.
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.

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

1 Answers1