Menu
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.
373
SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto increment. The keyword AUTOINCREMENT can be used with INTEGER field only.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
insert or ignore ... will insert the row(s) and ignore rows which violation any constraint (other than foreign key constraints).
Unless you can change the database structure you need to add the correct values in that other table to change your primary key value. That is "insert into table constraintingTable(key,val) values (A,B)" and then execute update tbl set a1 = A where a1 = KEY.
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
50
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
