SQLite Forum

Non primary key alias to rowid
Login
I am not an expert in the session extension either (I haven't used it at all), and I am a bit surprised that it can't handle the case of the unique identification of a data row being a unique text field instead of the somewhat arbitrary ROWID primary key.

If this is the case, my concept of a prefilter would be then when you bring the changeset in, you first fix your database to correct for ROWID issues with your text key. Assuming the changeset set comes in as an SQLite database, first, do a join to find records with the same ROWID but different text keys, and renumber those rows to some value unused in either the local database or the changeset. Then do a join to find records with the same text key but different ROWID, and renumber the ROWID in your local database to match. 

Now, you should have the case that ROWIDs and the text key are consistent, so tracking changes in one will track changes in the other. You MUST have your ROWID made explicit as an INTEGER PRIMARY KEY so a vacuum won't destroy numbering, but since the whole purpose of this was for FOREIGN KEYS that is basically assumed.