Generically identify the rowid causing a constraint conflict in SQLite
I need to identify the row that caused a constraint check to fail after an INSERT OR IGNORE statement.
I need this for a tool that generates SQL statements, so I can't tie this to a specific use case with a particular table.
The challenge here is that I can't know which constraints are set for the particular table that I will insert into. It may have foreign key or UNIQUE constraints, or even a CHECK clause attached.
So, all I can learn from the INSERT is that it has failed. Now, how to I tell which row broke the insert?
Clarification:
I'd need a solution that uses SQL statements / expressions, i.e. I cannot write non-SQL code around it.
Bonus question in case it's impossible in SQLite:
Can this be done in Postgresql or MySQL?
Example use case
Here's an example where this feature would be used (see the 2nd example and the note at the end which points back to this question): https://stackoverflow.com/a/53461201/43615
sql postgresql sqlite constraints check-constraints
asked Nov 25 '18 at 13:53
Thomas TempelmannThomas Tempelmann
5,82825094
|
show 1 more comment
I need to identify the row that caused a constraint check to fail after an INSERT OR IGNORE statement.
I need this for a tool that generates SQL statements, so I can't tie this to a specific use case with a particular table.
The challenge here is that I can't know which constraints are set for the particular table that I will insert into. It may have foreign key or UNIQUE constraints, or even a CHECK clause attached.
So, all I can learn from the INSERT is that it has failed. Now, how to I tell which row broke the insert?
Clarification:
I'd need a solution that uses SQL statements / expressions, i.e. I cannot write non-SQL code around it.
Bonus question in case it's impossible in SQLite:
Can this be done in Postgresql or MySQL?
Example use case
Here's an example where this feature would be used (see the 2nd example and the note at the end which points back to this question): https://stackoverflow.com/a/53461201/43615
sql postgresql sqlite constraints check-constraints
asked Nov 25 '18 at 13:53
Thomas TempelmannThomas Tempelmann
5,82825094
I don't know how to answer your exact requirement. Maybe SQLite has logging which can be turned on. If I had to find the offending row, offline, I would go into SQLite and run a query designed to flush out such rows. Would that not be a possibility for you?
– Tim Biegeleisen
Nov 25 '18 at 14:00
How exactly are the inserts done? Assume it has to be like some batch insert that pushes N rows at one command but how? Do all tables have any id/sequences even if not specific?
– pirho
Nov 25 '18 at 15:09
See the added link - it's about copying entire tables to a fresh set of tables, using anINSTEAD OF INSERTtrigger in which I then perform the optionalINSERT, and where I then need to find the existing row if the insert failed. I'm writing a generator for this kind of conversion and need to make this as universal as possible. It's a near-impossible task to parse the original DB's schema to figure out all constraints and create the matching SELECT statements for those.
– Thomas Tempelmann
Nov 25 '18 at 15:27
postgresql.org/docs/current/… ThePQPQresultVerboseErrorMessage()function does what you want. You only need to parse its text body:psycopg2.IntegrityError: duplicate key value violates unique constraint "tt_payload_key" DETAIL: Key (payload)=(OMG) already exists.
– wildplasser
Nov 25 '18 at 15:54
Of course this cannot be done in plain SQL. These functions exist to expose theSQLCA/SQLDA to any kind of frontend. (even the text-onlypsqlinterface handles them)
– wildplasser
Nov 25 '18 at 16:02
|
show 1 more comment
I need to identify the row that caused a constraint check to fail after an INSERT OR IGNORE statement.
I need this for a tool that generates SQL statements, so I can't tie this to a specific use case with a particular table.
The challenge here is that I can't know which constraints are set for the particular table that I will insert into. It may have foreign key or UNIQUE constraints, or even a CHECK clause attached.
So, all I can learn from the INSERT is that it has failed. Now, how to I tell which row broke the insert?
Clarification:
I'd need a solution that uses SQL statements / expressions, i.e. I cannot write non-SQL code around it.
Bonus question in case it's impossible in SQLite:
Can this be done in Postgresql or MySQL?
Example use case
Here's an example where this feature would be used (see the 2nd example and the note at the end which points back to this question): https://stackoverflow.com/a/53461201/43615
sql postgresql sqlite constraints check-constraints
asked Nov 25 '18 at 13:53
Thomas TempelmannThomas Tempelmann
5,82825094
I need to identify the row that caused a constraint check to fail after an INSERT OR IGNORE statement.
I need this for a tool that generates SQL statements, so I can't tie this to a specific use case with a particular table.
The challenge here is that I can't know which constraints are set for the particular table that I will insert into. It may have foreign key or UNIQUE constraints, or even a CHECK clause attached.
So, all I can learn from the INSERT is that it has failed. Now, how to I tell which row broke the insert?
Clarification:
I'd need a solution that uses SQL statements / expressions, i.e. I cannot write non-SQL code around it.
Bonus question in case it's impossible in SQLite:
Can this be done in Postgresql or MySQL?
Example use case
Here's an example where this feature would be used (see the 2nd example and the note at the end which points back to this question): https://stackoverflow.com/a/53461201/43615
sql postgresql sqlite constraints check-constraints
sql postgresql sqlite constraints check-constraints
asked Nov 25 '18 at 13:53
Thomas TempelmannThomas Tempelmann
5,82825094
asked Nov 25 '18 at 13:53
Thomas TempelmannThomas Tempelmann
5,82825094
edited Nov 25 '18 at 15:25
Thomas Tempelmann
asked Nov 25 '18 at 13:53
Thomas TempelmannThomas Tempelmann
5,82825094
asked Nov 25 '18 at 13:53
Thomas TempelmannThomas Tempelmann
5,82825094
asked Nov 25 '18 at 13:53
Thomas TempelmannThomas Tempelmann
5,82825094
5,82825094
I don't know how to answer your exact requirement. Maybe SQLite has logging which can be turned on. If I had to find the offending row, offline, I would go into SQLite and run a query designed to flush out such rows. Would that not be a possibility for you?
– Tim Biegeleisen
Nov 25 '18 at 14:00
How exactly are the inserts done? Assume it has to be like some batch insert that pushes N rows at one command but how? Do all tables have any id/sequences even if not specific?
– pirho
Nov 25 '18 at 15:09
See the added link - it's about copying entire tables to a fresh set of tables, using anINSTEAD OF INSERTtrigger in which I then perform the optionalINSERT, and where I then need to find the existing row if the insert failed. I'm writing a generator for this kind of conversion and need to make this as universal as possible. It's a near-impossible task to parse the original DB's schema to figure out all constraints and create the matching SELECT statements for those.
– Thomas Tempelmann
Nov 25 '18 at 15:27
postgresql.org/docs/current/… ThePQPQresultVerboseErrorMessage()function does what you want. You only need to parse its text body:psycopg2.IntegrityError: duplicate key value violates unique constraint "tt_payload_key" DETAIL: Key (payload)=(OMG) already exists.
– wildplasser
Nov 25 '18 at 15:54
Of course this cannot be done in plain SQL. These functions exist to expose theSQLCA/SQLDA to any kind of frontend. (even the text-onlypsqlinterface handles them)
– wildplasser
Nov 25 '18 at 16:02
|
show 1 more comment
I don't know how to answer your exact requirement. Maybe SQLite has logging which can be turned on. If I had to find the offending row, offline, I would go into SQLite and run a query designed to flush out such rows. Would that not be a possibility for you?
– Tim Biegeleisen
Nov 25 '18 at 14:00
How exactly are the inserts done? Assume it has to be like some batch insert that pushes N rows at one command but how? Do all tables have any id/sequences even if not specific?
– pirho
Nov 25 '18 at 15:09
See the added link - it's about copying entire tables to a fresh set of tables, using anINSTEAD OF INSERTtrigger in which I then perform the optionalINSERT, and where I then need to find the existing row if the insert failed. I'm writing a generator for this kind of conversion and need to make this as universal as possible. It's a near-impossible task to parse the original DB's schema to figure out all constraints and create the matching SELECT statements for those.
– Thomas Tempelmann
Nov 25 '18 at 15:27
postgresql.org/docs/current/… ThePQPQresultVerboseErrorMessage()function does what you want. You only need to parse its text body:psycopg2.IntegrityError: duplicate key value violates unique constraint "tt_payload_key" DETAIL: Key (payload)=(OMG) already exists.
– wildplasser
Nov 25 '18 at 15:54
Of course this cannot be done in plain SQL. These functions exist to expose theSQLCA/SQLDA to any kind of frontend. (even the text-onlypsqlinterface handles them)
– wildplasser
Nov 25 '18 at 16:02
I don't know how to answer your exact requirement. Maybe SQLite has logging which can be turned on. If I had to find the offending row, offline, I would go into SQLite and run a query designed to flush out such rows. Would that not be a possibility for you?
– Tim Biegeleisen
Nov 25 '18 at 14:00
I don't know how to answer your exact requirement. Maybe SQLite has logging which can be turned on. If I had to find the offending row, offline, I would go into SQLite and run a query designed to flush out such rows. Would that not be a possibility for you?
– Tim Biegeleisen
Nov 25 '18 at 14:00
How exactly are the inserts done? Assume it has to be like some batch insert that pushes N rows at one command but how? Do all tables have any id/sequences even if not specific?
– pirho
Nov 25 '18 at 15:09
How exactly are the inserts done? Assume it has to be like some batch insert that pushes N rows at one command but how? Do all tables have any id/sequences even if not specific?
– pirho
Nov 25 '18 at 15:09
See the added link - it's about copying entire tables to a fresh set of tables, using an
INSTEAD OF INSERT trigger in which I then perform the optional INSERT, and where I then need to find the existing row if the insert failed. I'm writing a generator for this kind of conversion and need to make this as universal as possible. It's a near-impossible task to parse the original DB's schema to figure out all constraints and create the matching SELECT statements for those.– Thomas Tempelmann
Nov 25 '18 at 15:27
See the added link - it's about copying entire tables to a fresh set of tables, using an
INSTEAD OF INSERT trigger in which I then perform the optional INSERT, and where I then need to find the existing row if the insert failed. I'm writing a generator for this kind of conversion and need to make this as universal as possible. It's a near-impossible task to parse the original DB's schema to figure out all constraints and create the matching SELECT statements for those.– Thomas Tempelmann
Nov 25 '18 at 15:27
postgresql.org/docs/current/… The
PQPQresultVerboseErrorMessage() function does what you want. You only need to parse its text body: psycopg2.IntegrityError: duplicate key value violates unique constraint "tt_payload_key" DETAIL: Key (payload)=(OMG) already exists.– wildplasser
Nov 25 '18 at 15:54
postgresql.org/docs/current/… The
PQPQresultVerboseErrorMessage() function does what you want. You only need to parse its text body: psycopg2.IntegrityError: duplicate key value violates unique constraint "tt_payload_key" DETAIL: Key (payload)=(OMG) already exists.– wildplasser
Nov 25 '18 at 15:54
Of course this cannot be done in plain SQL. These functions exist to expose theSQLCA/SQLDA to any kind of frontend. (even the text-only
psql interface handles them)– wildplasser
Nov 25 '18 at 16:02
Of course this cannot be done in plain SQL. These functions exist to expose theSQLCA/SQLDA to any kind of frontend. (even the text-only
psql interface handles them)– wildplasser
Nov 25 '18 at 16:02
|
show 1 more comment
1 Answer
1
active
oldest
votes
This is not possible even in theory.
Not all constraint failures involve data outside the row being inserted. For instance, CHECK constraints often will not. There would be no row id to report in this case.
An INSERT could fail multiple constraints applied to different rows in other tables. Which one would you be interested in?
If there's a way to find out what table a row is in based on the row id, I don't know what it is, so having the raw row id would probably not be much use.
Finally, row ids are optional in SQLite; you can create a table with the row id column using WITHOUT ROWID.
answered Nov 26 '18 at 2:19
Larry LustigLarry Lustig
40.3k1284130
I understand that it's not always possible. But in my specific use case I copy data from one database to another, and any "general" constraints were already met and won't lead to errors. The copy has added tables that may haveuniqueconstraints, and my (ordered) inserts would then indeed point to one specific failed row (without depenencies on other tables) due to those added constraints. But I accept that there is no such provision in SQL (SQLite, Postgresql), so my tool won't be perfect. Best I can do is to test whether I could detect the failed row, andRAISEotherwise.
– Thomas Tempelmann
Nov 26 '18 at 12:21
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%2f53468186%2fgenerically-identify-the-rowid-causing-a-constraint-conflict-in-sqlite%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
This is not possible even in theory.
Not all constraint failures involve data outside the row being inserted. For instance, CHECK constraints often will not. There would be no row id to report in this case.
An INSERT could fail multiple constraints applied to different rows in other tables. Which one would you be interested in?
If there's a way to find out what table a row is in based on the row id, I don't know what it is, so having the raw row id would probably not be much use.
Finally, row ids are optional in SQLite; you can create a table with the row id column using WITHOUT ROWID.
answered Nov 26 '18 at 2:19
Larry LustigLarry Lustig
40.3k1284130
I understand that it's not always possible. But in my specific use case I copy data from one database to another, and any "general" constraints were already met and won't lead to errors. The copy has added tables that may haveuniqueconstraints, and my (ordered) inserts would then indeed point to one specific failed row (without depenencies on other tables) due to those added constraints. But I accept that there is no such provision in SQL (SQLite, Postgresql), so my tool won't be perfect. Best I can do is to test whether I could detect the failed row, andRAISEotherwise.
– Thomas Tempelmann
Nov 26 '18 at 12:21
add a comment |
This is not possible even in theory.
Not all constraint failures involve data outside the row being inserted. For instance, CHECK constraints often will not. There would be no row id to report in this case.
An INSERT could fail multiple constraints applied to different rows in other tables. Which one would you be interested in?
If there's a way to find out what table a row is in based on the row id, I don't know what it is, so having the raw row id would probably not be much use.
Finally, row ids are optional in SQLite; you can create a table with the row id column using WITHOUT ROWID.
answered Nov 26 '18 at 2:19
Larry LustigLarry Lustig
40.3k1284130
I understand that it's not always possible. But in my specific use case I copy data from one database to another, and any "general" constraints were already met and won't lead to errors. The copy has added tables that may haveuniqueconstraints, and my (ordered) inserts would then indeed point to one specific failed row (without depenencies on other tables) due to those added constraints. But I accept that there is no such provision in SQL (SQLite, Postgresql), so my tool won't be perfect. Best I can do is to test whether I could detect the failed row, andRAISEotherwise.
– Thomas Tempelmann
Nov 26 '18 at 12:21
add a comment |
This is not possible even in theory.
Not all constraint failures involve data outside the row being inserted. For instance, CHECK constraints often will not. There would be no row id to report in this case.
An INSERT could fail multiple constraints applied to different rows in other tables. Which one would you be interested in?
If there's a way to find out what table a row is in based on the row id, I don't know what it is, so having the raw row id would probably not be much use.
Finally, row ids are optional in SQLite; you can create a table with the row id column using WITHOUT ROWID.
answered Nov 26 '18 at 2:19
Larry LustigLarry Lustig
40.3k1284130
This is not possible even in theory.
Not all constraint failures involve data outside the row being inserted. For instance, CHECK constraints often will not. There would be no row id to report in this case.
An INSERT could fail multiple constraints applied to different rows in other tables. Which one would you be interested in?
If there's a way to find out what table a row is in based on the row id, I don't know what it is, so having the raw row id would probably not be much use.
Finally, row ids are optional in SQLite; you can create a table with the row id column using WITHOUT ROWID.
answered Nov 26 '18 at 2:19
Larry LustigLarry Lustig
40.3k1284130
edited Nov 26 '18 at 2:25
answered Nov 26 '18 at 2:19
Larry LustigLarry Lustig
40.3k1284130
answered Nov 26 '18 at 2:19
Larry LustigLarry Lustig
40.3k1284130
answered Nov 26 '18 at 2:19
Larry LustigLarry Lustig
40.3k1284130
40.3k1284130
I understand that it's not always possible. But in my specific use case I copy data from one database to another, and any "general" constraints were already met and won't lead to errors. The copy has added tables that may haveuniqueconstraints, and my (ordered) inserts would then indeed point to one specific failed row (without depenencies on other tables) due to those added constraints. But I accept that there is no such provision in SQL (SQLite, Postgresql), so my tool won't be perfect. Best I can do is to test whether I could detect the failed row, andRAISEotherwise.
– Thomas Tempelmann
Nov 26 '18 at 12:21
add a comment |
I understand that it's not always possible. But in my specific use case I copy data from one database to another, and any "general" constraints were already met and won't lead to errors. The copy has added tables that may haveuniqueconstraints, and my (ordered) inserts would then indeed point to one specific failed row (without depenencies on other tables) due to those added constraints. But I accept that there is no such provision in SQL (SQLite, Postgresql), so my tool won't be perfect. Best I can do is to test whether I could detect the failed row, andRAISEotherwise.
– Thomas Tempelmann
Nov 26 '18 at 12:21
I understand that it's not always possible. But in my specific use case I copy data from one database to another, and any "general" constraints were already met and won't lead to errors. The copy has added tables that may have
unique constraints, and my (ordered) inserts would then indeed point to one specific failed row (without depenencies on other tables) due to those added constraints. But I accept that there is no such provision in SQL (SQLite, Postgresql), so my tool won't be perfect. Best I can do is to test whether I could detect the failed row, and RAISEotherwise.– Thomas Tempelmann
Nov 26 '18 at 12:21
I understand that it's not always possible. But in my specific use case I copy data from one database to another, and any "general" constraints were already met and won't lead to errors. The copy has added tables that may have
unique constraints, and my (ordered) inserts would then indeed point to one specific failed row (without depenencies on other tables) due to those added constraints. But I accept that there is no such provision in SQL (SQLite, Postgresql), so my tool won't be perfect. Best I can do is to test whether I could detect the failed row, and RAISEotherwise.– Thomas Tempelmann
Nov 26 '18 at 12:21
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.
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%2f53468186%2fgenerically-identify-the-rowid-causing-a-constraint-conflict-in-sqlite%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
I don't know how to answer your exact requirement. Maybe SQLite has logging which can be turned on. If I had to find the offending row, offline, I would go into SQLite and run a query designed to flush out such rows. Would that not be a possibility for you?
– Tim Biegeleisen
Nov 25 '18 at 14:00
How exactly are the inserts done? Assume it has to be like some batch insert that pushes N rows at one command but how? Do all tables have any id/sequences even if not specific?
– pirho
Nov 25 '18 at 15:09
See the added link - it's about copying entire tables to a fresh set of tables, using an
INSTEAD OF INSERTtrigger in which I then perform the optionalINSERT, and where I then need to find the existing row if the insert failed. I'm writing a generator for this kind of conversion and need to make this as universal as possible. It's a near-impossible task to parse the original DB's schema to figure out all constraints and create the matching SELECT statements for those.– Thomas Tempelmann
Nov 25 '18 at 15:27
postgresql.org/docs/current/… The
PQPQresultVerboseErrorMessage()function does what you want. You only need to parse its text body:psycopg2.IntegrityError: duplicate key value violates unique constraint "tt_payload_key" DETAIL: Key (payload)=(OMG) already exists.– wildplasser
Nov 25 '18 at 15:54
Of course this cannot be done in plain SQL. These functions exist to expose theSQLCA/SQLDA to any kind of frontend. (even the text-only
psqlinterface handles them)– wildplasser
Nov 25 '18 at 16:02