How to do a insert or replace when the primary key will not be used

Question

I have a sqlite database on a multi device android system that requires UUID (yes it requires them), however I do not set the UUID as the primary key, instead the UUID is just a column and the primary key is just an INTEGER on each device (that is not transferred) but instead auto generated on the local android database

I am doing an Update (if failed) insert routine based on if the UUID exists -which is quite slow and eventually I want to convert this into a bulk system with the SQLITESTATEMENT

however in order for me to get to that point I need to make the INSERT OR REPLACE (or REPLACE INTO) work with my data set - From what I understand it only works based on if the primary key already exists - since my primary key is not what I actually care about how do I make the Replace Into work based of a different column? Or perhaps I can do bulk inserts a different way - that does not involve replace intos - but can still handle inserts or updates -

any help is appreciated

Some of the items I have been using for references How do I UPDATE a row in a table or INSERT it if it doesn't exist?

http://www.buzzingandroid.com/2013/01/sqlite-insert-or-replace-through-contentprovider/

Improve INSERT-per-second performance of SQLite?

How do I use prepared statements in SQlite in Android?

http://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html#execute() Android SQLite database: slow insertion

Joachim Isaksson · Accepted Answer · 2013-10-12 16:47:24Z

3

You can use INSERT OR REPLACE even if the UUID is not the primary key as long as you make the UUID field UNIQUE;

CREATE TABLE test ( id INTEGER PRIMARY KEY AUTOINCREMENT, uuid UUID UNIQUE NOT NULL );

Any insert that violates the UNIQUE constraint replaces the existing row (but generates a new primary key for the new row)

A very simple SQLfiddle to test with.

edited Oct 12, 2013 at 16:47

answered Oct 12, 2013 at 16:39

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GregM Over a year ago

Awesome - exactly what I was looking for - Thank you for that SQLfiddle as well - a handy tool... This leads to another question but related - would I use this to execute this call? developer.android.com/reference/android/database/sqlite/…

Joachim Isaksson Over a year ago

@morty346 I've not used that very method, but yes, it certainly looks like the correct method to use.

Collectives™ on Stack Overflow

How to do a insert or replace when the primary key will not be used

1 Answer 1

2 Comments

Hot Network Questions