How to do SQL Insert or Replace like operation without changing primary key?

1
votes

I'm trying to insert a record into a SQLite database table. The table has a Unique constraint on a column "URL" and a primary key auto increment integer "_id".

I'm using insert or replace as my conflict resolution, since I need the new data to be persisted. However, this method is incrementing the primary key and messing up some foreign keys in other tables.

What's the best way to overwrite the record without updating the _id?

2
androidsqldatabasesqlite
I only need to update if the unique constraints are violated, which is why insert or replace is so convinent. Is there an easy way to check those constraints? - CodeFusionMobile
"I'm using insert or replace as my conflict resolution." What does this mean? Are you updating these records by deleting the old one, then inserting a new one? - Matthew Cox

2 Answers

5
votes

The simple answer is to stop using Replace syntax. This causes the old record to be deleted then a new one added ... which would increment your index.

Utilize the UPDATE syntax to handle conflicts instead

EDIT:

If you are really partial to the Replace syntax then it will come at a cost. You will need to write additional code that updates all prev occurrences of the old index to the new one. Not overly hard but this will correct the issue of synchronizing indexes

Documentation [Listed under REPLACE section little ways down the page]: http://www.sqlite.org/lang_conflict.html

1
votes

this is my code of SQLite

"CREATE TABLE IF NOT EXISTS posts (" +
"_id integer NOT NULL," +
"id_language integer NOT NULL" +
");" +
"CREATE UNIQUE INDEX posts_idx ON posts(_id, id_language);";


"INSERT OR REPLACE INTO " + DB_TABLE + " (" + formulateColumns() + ") " +
            "VALUES (" + formulateValues(v) + ");");