通常在SQLite中标识导致约束冲突的rowid [英] Generically identify the rowid causing a constraint conflict in SQLite

问题描述

我需要确定在INSERT OR IGNORE语句之后导致约束检查失败的行.

I need to identify the row that caused a constraint check to fail after an INSERT OR IGNORE statement.

我需要此工具来生成SQL语句，因此我无法将其绑定到具有特定表的特定用例.

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.

这里的挑战是我不知道为要插入的特定表设置了哪些约束.它可能具有外键或UNIQUE约束，甚至可能附加了CHECK子句.

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.

因此，我可以从INSERT中学到的只是它失败了.现在，我如何知道插入的哪一行?

So, all I can learn from the INSERT is that it has failed. Now, how to I tell which row broke the insert?

说明:

Clarification:

我需要一个使用SQL语句/表达式的解决方案，即我无法在其周围编写非SQL代码.

I'd need a solution that uses SQL statements / expressions, i.e. I cannot write non-SQL code around it.

在SQLite中无法使用时出现的奖励问题:

Bonus question in case it's impossible in SQLite:

这可以在Postgresql或MySQL中完成吗?

Can this be done in Postgresql or MySQL?

用例示例

Example use case

以下是将使用此功能的示例(请参阅第二个示例和末尾的注释，指向该问题): https://stackoverflow.com/a/53461201/43615

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

推荐答案

即使从理论上讲也是不可能的.

This is not possible even in theory.

并非所有约束失败都涉及要插入的行之外的数据.例如，CHECK约束通常不会.在这种情况下，将没有行ID可以报告.

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.

INSERT可能会使应用于其他表中不同行的多个约束失败.您会对哪一个感兴趣?

An INSERT could fail multiple constraints applied to different rows in other tables. Which one would you be interested in?

如果有一种方法可以根据行ID找出行所在的表，则我不知道它是什么，因此拥有原始行ID可能用处不大

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.

最后，在SQLite中，行ID是可选的；您可以使用WITHOUT ROWID用行ID列创建一个表.

Finally, row ids are optional in SQLite; you can create a table with the row id column using WITHOUT ROWID.

这篇关于通常在SQLite中标识导致约束冲突的rowid的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！