SQL 同时选择和插入时出现sqlite和“约束失败”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5322669/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sqlite and 'constraint failed' error while select and insert at the same time
提问by Evgen Bodunov
I'm working on migration function. It reads data from old table and inserts it into the new one. All that stuff working in background thread with low priority.
我正在研究迁移功能。它从旧表中读取数据并将其插入到新表中。所有这些东西都在低优先级的后台线程中工作。
My steps in pseudo code.
我在伪代码中的步骤。
sqlite3_prepare_stmt (select statement)
sqlite3_prepare_stmt (insert statement)
while (sqlite3_step (select statement) == SQLITE_ROW)
{
get data from select row results
sqlite3_bind select results to insert statement
sqlite3_step (insert statement)
sqlite3_reset (insert statement)
}
sqlite3_reset (select statement)
I'm always getting 'constraint failed' error on sqlite3_step (insert statement). Why it's happend and how i could fix that?
我总是收到“约束失败”错误sqlite3_step (insert statement)。为什么会发生这种情况，我该如何解决？
UPD:As i'm understand that's happend because background thread use db handle opened in main thread. Checking that guess now.
UPD：据我所知，这是因为后台线程使用在主线程中打开的数据库句柄。现在检查那个猜测。
UPD2:
UPD2：
sqlite> select sql from sqlite_master where tbl_name = 'tiles';
CREATE TABLE tiles('pk' INTEGER PRIMARY KEY, 'data' BLOB, 'x' INTEGER, 'y' INTEGER, 'z' INTEGER, 'importKey' INTEGER)
sqlite> select sql from sqlite_master where tbl_name = 'tiles_v2';
CREATE TABLE tiles_v2 (pk int primary key, x int, y int, z int, layer int, data blob, timestamp real)
回答by Mike Sherrill 'Cat Recall'
It probably means your insert statement is violating a constraint in the new table. Could be a primary key constraint, a unique constraint, a foreign key constraint (if you're using PRAGMA foreign_keys = ON;), and so on.
这可能意味着您的插入语句违反了新表中的约束。可能是主键约束、唯一约束、外键约束（如果您正在使用PRAGMA foreign_keys = ON;）等。
You fix that either by dropping the constraint, correcting the data, or dropping the data. Dropping the constraint is usually a Bad Thing, but that depends on the application.
您可以通过删除约束、更正数据或删除数据来解决该问题。删除约束通常是一件坏事，但这取决于应用程序。
Is there a compelling reason to copy data one row at a time instead of as a set?
一次复制一行数据而不是一组复制数据是否有令人信服的理由？
INSERT INTO new_table
SELECT column_list FROM old_table;
If you need help identifying the constraint, edit your original question, and post the output of these two SQLite queries.
如果您需要帮助识别约束，请编辑您的原始问题，并发布这两个 SQLite 查询的输出。
select sql from sqlite_master where tbl_name = 'old_table_name';
select sql from sqlite_master where tbl_name = 'new_table_name';
Update:Based on the output of those two queries, I see only one constraint--the primary key constraint in each table. If you haven't built any triggers on these tables, the only constraint that can fail is the primary key constraint. And the only way that constraint can fail is if you try to insert two rows that have the same value for 'pk'.
更新：根据这两个查询的输出，我只看到一个约束——每个表中的主键约束。如果您尚未在这些表上构建任何触发器，则唯一可能失败的约束是主键约束。并且约束失败的唯一方法是如果您尝试插入具有相同 'pk' 值的两行。
I suppose that could happen in a few different ways.
我想这可能会以几种不同的方式发生。
- The old table has duplicate values in the 'pk' column.
- The code that does your migration alters or injects a duplicate value before inserting data into your new table.
- Another process, possibly running on a different computer, is inserting or updating data without your knowledge.
- Other reasons I haven't thought of yet. :-)
- 旧表在“pk”列中有重复的值。
- 在将数据插入新表之前，执行迁移的代码会更改或注入重复值。
- 另一个可能在不同计算机上运行的过程是在您不知情的情况下插入或更新数据。
- 其他原因暂时没想到。:-)
You can determine whether there are duplicate values of 'pk' in the old table by running this query.
您可以通过运行此查询来确定旧表中是否存在重复的 'pk' 值。
select pk
from old_table_name
group by pk
having count() > 1;
You might consider trying to manually migrate the data using INSERT INTO . . . SELECT . . .If that fails, add a WHERE clause to reduce the size of the set until you isolate the bad data.
您可能会考虑尝试使用手动迁移数据INSERT INTO . . . SELECT . . .如果失败，请添加 WHERE 子句以减小集合的大小，直到隔离坏数据。
回答by dotNET
Just in case anyone lands here looking for "Constraint failed" error message, make sure your Idcolumn's type is INTEGER, not INTEGER (0, 15)or something.
以防万一有人在这里寻找“约束失败”错误消息，请确保您的Id列的类型是INTEGER，不是INTEGER (0, 15)或其他类型。
Background
背景
If your table has a column named Id, with type INTEGERand set as Primary Key, SQLite treats it as an alias for the built-in column RowId. This column works like an auto-increment column. In my case, this column was working fine till some table designer (probably the one created by SQLite guys for Visual Studio) changed column type from INTEGERto INTEGER (0, 15)and all of a sudden my application started throwing Constraint failedexception.
如果您的表有一个名为 的列Id，其类型INTEGER和设置为主键，SQLite 会将其视为内置列的别名RowId。此列的工作方式类似于自动增量列。就我而言，此列一直工作正常，直到某些表设计器（可能是 SQLite 人员为 Visual Studio 创建的表设计器）将列类型从 更改为INTEGER，INTEGER (0, 15)然后我的应用程序突然开始抛出Constraint failed异常。
回答by a_m_dev
just for making it more clearer :
make sure that you use data type correct , i was using intinstead of integeron creating tmy tables like this :
只是为了让它更清楚：
确保您使用的数据类型正确，我使用int而不是integer创建这样的 tmy 表：
id int primary key not null
id int primary key not null
and i was stuckon the constrain problem over hours ...
just make sure that to enter data type correctly in creating database.
我在几个小时内被困在约束问题上......
只需确保在创建数据库时正确输入数据类型。
回答by Don White
I have found that troubleshooting foreign key constraint errors with sqlite to be troublesome, particularly on large data sets. However the following approach helps identify the offending relation.
我发现使用 sqlite 排除外键约束错误很麻烦，尤其是在大型数据集上。但是，以下方法有助于识别违规关系。
- disable foreign key checking:
PRAGMA foreign_keys = 0; - execute the statement that cause the error - in my case it was an INSERT of 70,000 rows with 3 different foreign key relations.
- re-enable foreign key checking:
PRAGMA foreign_keys = 1; - identify the foreign key errors:
PRAGMA foreign_key_check(table-name);
- 禁用外键检查：
PRAGMA foreign_keys = 0; - 执行导致错误的语句 - 在我的情况下，它是 70,000 行的 INSERT，具有 3 个不同的外键关系。
- 重新启用外键检查：
PRAGMA foreign_keys = 1; - 识别外键错误：
PRAGMA foreign_key_check(table-name);
In my case, it showed the 13 rows with invalid references.
就我而言，它显示了 13 行的无效引用。
回答by Amir Fo
One of the reasons that this error occurs is inserting duplicated data for a unique row too. Check your all unique and keys for duplicated data.
发生此错误的原因之一是也为唯一行插入了重复数据。检查所有唯一和键是否有重复的数据。
