CODEWITHSUNDEEP
androiddatabasesqlite
dd619
dd619

Reputation: 6170

Does sqlite "insert or replace" work with AUTOINCREMENT primary key?

I have a database in which i have created one table with name "SectionDetails". In this table i have set "id" as a primary key with AUTOINCREMENT property.And i am inserting my data into this table.

However, i came to the scenario where i need to check if record i am inserting is already present or not(if record is present then replace it with same values or skip it,And if record is not present then insert new one ).

But when i tried to insert record with same column values, it increases the primary key and insert the same row again instead of replacing.

So, my question is- Does sqlite "insert or replace" works with AUTOINCREMENT primary key?

I am using following query:

insert or replace into SectionDetails(Name,Month,Title,Url)values('Deepak',"August","None","www.dd619.com")

here column "id" is not appearing because its a primary key with AUTOINCREMENT property.

Upvotes: 12

Views: 11280

Answers (2)

P_M
P_M

Reputation: 328

Short answer: no it does not.

The "insert or replace" only works well when you also specify the primary key. With the autoincrement in a setup that you describe, this cannot work.

Probably the quickest way to do that is by creating a custom insert/update method for your use case.

Upvotes: 12

laalto
laalto

Reputation: 152817

You will need to add some unique constraints to your other columns to make this work and even then you will have your IDs change.

insert or replace is really an insert with on conflict replace conflict resolution strategy. That is, when the insert would violate some constraint, all conflicting rows are first deleted and the insert takes place only then. The autoincrement mechanism will then generate a new ID value for you.

For more information: http://www.sqlite.org/lang_conflict.html

Upvotes: 21

Related Questions