CODEWITHSUNDEEP
androidsqldatabasesqlite
CodeFusionMobile
CodeFusionMobile

Reputation: 15110

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

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?

Upvotes: 1

Views: 4834

Answers (2)

Damir Mailybayev
Damir Mailybayev

Reputation: 1081

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) + ");");

Upvotes: 1

Matthew Cox
Matthew Cox

Reputation: 13672

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

Upvotes: 5

Related Questions