Generically identify the rowid causing a constraint conflict in SQLite












0















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







share|improve this question

























  • 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 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













  • 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
















0















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







share|improve this question

























  • 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 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













  • 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














0












0








0








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







share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 25 '18 at 15:25







Thomas Tempelmann

















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 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













  • 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



















  • 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 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













  • 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

















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












1 Answer
1






active

oldest

votes


















0














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.






share|improve this answer


























  • 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













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
});


}
});














draft saved

draft discarded


















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









0














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.






share|improve this answer


























  • 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


















0














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.






share|improve this answer


























  • 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
















0












0








0







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.






share|improve this answer















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.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 26 '18 at 2:25

























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 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



















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






















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







-

Popular posts from this blog

Costa Masnaga

Image
.mw-parser-output .avviso .mbox-text-div>div,.mw-parser-output .avviso .mbox-text-full-div>div{font-size:90%}.mw-parser-output .avviso .mbox-image div{width:52px}.mw-parser-output .avviso .mbox-text-full-div .hide-when-compact{display:block} Questa voce o sezione sull'argomento Lombardia non cita le fonti necessarie o quelle presenti sono insufficienti . Puoi migliorare questa voce aggiungendo citazioni da fonti attendibili secondo le linee guida sull'uso delle fonti. Segui i suggerimenti del progetto di riferimento. Costa Masnaga comune Localizzazione Stato   Italia Regione Lombardia Provincia Lecco Amministrazione Sindaco Sabina Panzeri (Al centro il cittadino) dall'08/06/2009 Territorio Coordinate 45°46′N 9°17′E  /  45.766667°N 9.283333°E 45.766667; 9.283333  ( Costa Masnaga ) Coordinate: 45°46′N 9°17′E  /  45.766667°N 9.283333°E 45.766667; 9.283333  ( Costa Masnaga ) Altitudine 318 m s.l.m...
Read more

Fotorealismo

Image
Guerrino Boatto, Venice's St. Barnaba Church and canal Il fotorealismo è un genere di pittura basato sull'uso di una o più fotografie dalle quali l'artista prende le informazioni necessarie da utilizzare poi nel processo della creazione dell'opera, in genere ad olio o ad acrilico, al fine di avere un aspetto finale il più simile possibile alla fotografia. Indice 1 Storia 2 Lista dei fotorealisti 3 Note 4 Voci correlate 5 Altri progetti Storia | Il fotorealismo è un movimento che nasce negli Stati Uniti d'America alla fine degli anni sessanta [1] sulla scia della Pop Art [2] [3] [4] , e insieme condividono il carattere reazionario verso la travolgente ascesa dei media, che alla metà del ventesimo secolo si trasforma in un fenomeno di massa talmente imponente da minacciare il valore dell'immagine nell'arte. [5] [6] Ne consegue così che, colto il gesto d'inizio che fu della Pop Art, i fotorealisti aprono ad un ...
Read more

Sidney Franklin

Image
Questa voce sull'argomento registi statunitensi è solo un abbozzo . Contribuisci a migliorarla secondo le convenzioni di Wikipedia. Sidney Franklin, foto di Carl Van Vechten Oscar alla memoria Irving G. Thalberg 1943 Sidney Franklin , all'anagrafe Sidney Arnold Franklin , (San Francisco, 21 marzo 1893 – Santa Monica, 18 maggio 1972), è stato un regista, produttore cinematografico e attore statunitense. Il 20 giugno 1963, si sposò con l'attrice australiana Enid Bennett. Il matrimonio durò fino alla morte dell'attrice, il 14 maggio 1969 [1] Indice 1 Filmografia 1.1 Regista 1.2 Aiuto regia (parziale) 1.3 Produttore 1.4 Attore (parziale) 2 Note 3 Altri progetti 4 Collegamenti esterni Filmografia | Regista | The Baby , co-regia Chester M. Franklin (1915) The Rivals , co-regia Chester M. Franklin (1915) Little Dick's First Case , co-regia Chester M. Franklin (1915) Her Filmland Hero , co-...
Read more