Retain the value of primary key when using conflict strategy as REPLACE in SQLite
I created a SQLite table as follows
CREATE TABLE event
(_id INTEGER PRIMARY KEY AUTOINCREMENT,
event_id TEXT NOT NULL,
instance_id TEXT NOT NULL,
....,
UNIQUE (event_id, instance_id) ON CONFLICT REPLACE)
Now when there is a conflict while inserting, SQLite replaces the row with a new row, which in turn changes my primary key _id.
I don't want to change my primary key when it is replaced (on conflict). What should I do?
sql sqlite android-sqlite
edited Nov 13 at 9:11
Vadim Kotov
4,30153247
asked Nov 13 at 9:00
Vignesh Muralidharan
34
add a comment |
I created a SQLite table as follows
CREATE TABLE event
(_id INTEGER PRIMARY KEY AUTOINCREMENT,
event_id TEXT NOT NULL,
instance_id TEXT NOT NULL,
....,
UNIQUE (event_id, instance_id) ON CONFLICT REPLACE)
Now when there is a conflict while inserting, SQLite replaces the row with a new row, which in turn changes my primary key _id.
I don't want to change my primary key when it is replaced (on conflict). What should I do?
sql sqlite android-sqlite
edited Nov 13 at 9:11
Vadim Kotov
4,30153247
asked Nov 13 at 9:00
Vignesh Muralidharan
34
add a comment |
I created a SQLite table as follows
CREATE TABLE event
(_id INTEGER PRIMARY KEY AUTOINCREMENT,
event_id TEXT NOT NULL,
instance_id TEXT NOT NULL,
....,
UNIQUE (event_id, instance_id) ON CONFLICT REPLACE)
Now when there is a conflict while inserting, SQLite replaces the row with a new row, which in turn changes my primary key _id.
I don't want to change my primary key when it is replaced (on conflict). What should I do?
sql sqlite android-sqlite
edited Nov 13 at 9:11
Vadim Kotov
4,30153247
asked Nov 13 at 9:00
Vignesh Muralidharan
34
I created a SQLite table as follows
CREATE TABLE event
(_id INTEGER PRIMARY KEY AUTOINCREMENT,
event_id TEXT NOT NULL,
instance_id TEXT NOT NULL,
....,
UNIQUE (event_id, instance_id) ON CONFLICT REPLACE)
Now when there is a conflict while inserting, SQLite replaces the row with a new row, which in turn changes my primary key _id.
I don't want to change my primary key when it is replaced (on conflict). What should I do?
sql sqlite android-sqlite
sql sqlite android-sqlite
edited Nov 13 at 9:11
Vadim Kotov
4,30153247
asked Nov 13 at 9:00
Vignesh Muralidharan
34
edited Nov 13 at 9:11
Vadim Kotov
4,30153247
asked Nov 13 at 9:00
Vignesh Muralidharan
34
edited Nov 13 at 9:11
Vadim Kotov
4,30153247
edited Nov 13 at 9:11
Vadim Kotov
4,30153247
edited Nov 13 at 9:11
Vadim Kotov
4,30153247
4,30153247
asked Nov 13 at 9:00
Vignesh Muralidharan
34
asked Nov 13 at 9:00
Vignesh Muralidharan
34
asked Nov 13 at 9:00
Vignesh Muralidharan
34
34
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
ON REPLACE deletes the row with the conflict and inserts a new one. So, yes, the rowid is going to change unless it's included in the values being inserted.
If you're using sqlite 3.24 or newer, a different type of conflict resolution is available that allows you to update only certain columns when a particular unique constraint is violated. It's known as UPSERT. That's your best option for this case.
The other option involves trying to insert, detecting a uniqueness error, and doing an update instead. I'd only go this route if stuck on an old version.
Edit: obligatory do you really need AUTOINCREMENT?
answered Nov 13 at 10:08
Shawn
3,4681613
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53277235%2fretain-the-value-of-primary-key-when-using-conflict-strategy-as-replace-in-sqlit%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
ON REPLACE deletes the row with the conflict and inserts a new one. So, yes, the rowid is going to change unless it's included in the values being inserted.
If you're using sqlite 3.24 or newer, a different type of conflict resolution is available that allows you to update only certain columns when a particular unique constraint is violated. It's known as UPSERT. That's your best option for this case.
The other option involves trying to insert, detecting a uniqueness error, and doing an update instead. I'd only go this route if stuck on an old version.
Edit: obligatory do you really need AUTOINCREMENT?
answered Nov 13 at 10:08
Shawn
3,4681613
add a comment |
ON REPLACE deletes the row with the conflict and inserts a new one. So, yes, the rowid is going to change unless it's included in the values being inserted.
If you're using sqlite 3.24 or newer, a different type of conflict resolution is available that allows you to update only certain columns when a particular unique constraint is violated. It's known as UPSERT. That's your best option for this case.
The other option involves trying to insert, detecting a uniqueness error, and doing an update instead. I'd only go this route if stuck on an old version.
Edit: obligatory do you really need AUTOINCREMENT?
answered Nov 13 at 10:08
Shawn
3,4681613
add a comment |
ON REPLACE deletes the row with the conflict and inserts a new one. So, yes, the rowid is going to change unless it's included in the values being inserted.
If you're using sqlite 3.24 or newer, a different type of conflict resolution is available that allows you to update only certain columns when a particular unique constraint is violated. It's known as UPSERT. That's your best option for this case.
The other option involves trying to insert, detecting a uniqueness error, and doing an update instead. I'd only go this route if stuck on an old version.
Edit: obligatory do you really need AUTOINCREMENT?
answered Nov 13 at 10:08
Shawn
3,4681613
ON REPLACE deletes the row with the conflict and inserts a new one. So, yes, the rowid is going to change unless it's included in the values being inserted.
If you're using sqlite 3.24 or newer, a different type of conflict resolution is available that allows you to update only certain columns when a particular unique constraint is violated. It's known as UPSERT. That's your best option for this case.
The other option involves trying to insert, detecting a uniqueness error, and doing an update instead. I'd only go this route if stuck on an old version.
Edit: obligatory do you really need AUTOINCREMENT?
answered Nov 13 at 10:08
Shawn
3,4681613
answered Nov 13 at 10:08
Shawn
3,4681613
answered Nov 13 at 10:08
Shawn
3,4681613
answered Nov 13 at 10:08
Shawn
3,4681613
3,4681613
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53277235%2fretain-the-value-of-primary-key-when-using-conflict-strategy-as-replace-in-sqlit%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
