SQLite Forum

How to handle collation version changes
Login
> I think it also means that it's OK for the collation to change completely - for example, it could even be backwards or otherwise entirely different from before... And for the REINDEX command, the old version of the collation is not needed/completely irrelevant - it just needs to have the new version loaded. Is that understanding all correct?

I confirmed this understanding offline.

Also, as Bill mentioned, a collation change could mean that two things that used to be distinct are now the same, no any UNIQUE constraint on a column with a collation could be violated by REINDEX, causing the REINDEX to fail.

So I think the only two points to be aware of here are:

1. When changing the implementation of a collation, call REINDEX right after loading the new collation and before touching any data impacted by the new collation.
2. Be aware that REINDEX with a UNIQUE constraint may fail if the collation change results in uniqueness changes.