Well, you probably know already not to use natural primary keys in your database design. Here’s another story how ignoring this rule can and will bite you.

Breaking the Rule(s)

New to Android and SQLite I stumbled upon SQLite’s “UPSERT” ability: if a record exists, UPDATE it, otherwise INSERT a new one. The caveat is that the “UPSERT” consists of a DELETE followed by an INSERT which means the record will get a new ID if it comes from an PRIMARY KEY field. And of course it will lose any existing data that is not repeated in the “UPSERT”.

Both were not a problem for me as I got my data from a 3rd party web service that would give values for all columns anyway and only a natural key. So the SQL to create the table looked like this: