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