sqlite 和“约束失败”同时选择和插入时出错

Question

我正在研究迁移功能。它从旧表中读取数据并将其插入到新表中。所有这些东西都在低优先级的后台线程中工作。

我的伪代码步骤。

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)

我总是在 sqlite3_step（插入语句） 上收到“约束失败”错误。为什么会发生这种情况以及我该如何解决这个问题？

UPD：据我了解，发生这种情况是因为后台线程使用主线程中打开的数据库句柄。现在检查这个猜测。

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)

Answer 1

这可能意味着您的插入语句违反了新表中的约束。可能是主键约束、唯一约束、外键约束（如果您使用的是PRAGMAforeign_keys = ON;）等等。

您可以通过删除约束、更正数据或删除数据来解决此问题。放弃约束通常是一件坏事，但这取决于应用程序。

是否有令人信服的理由一次复制一行数据而不是一组数据？

INSERT INTO new_table
SELECT column_list FROM old_table;

如果您需要帮助识别约束，请编辑您的原始问题，并发布这两个 SQLite 查询的输出。

select sql from sqlite_master where tbl_name = 'old_table_name';
select sql from sqlite_master where tbl_name = 'new_table_name';

---

更新：根据这两个查询的输出，我只看到一个约束——每个表中的主键约束。如果您尚未在这些表上构建任何触发器，则唯一可能失败的约束是主键约束。该约束失败的唯一方法是尝试插入具有相同“pk”值的两行。

我想这可能会以几种不同的方式发生。

• 旧表中有重复值
  “pk”列。
• 执行迁移的代码
  更改或注入重复值
  在将数据插入新的之前
  桌子。
• 另一个进程，可能正在运行
  另一台计算机，正在插入或
  在您不知情的情况下更新数据。
• 其他原因我还没想到。 :-)

您可以通过运行此查询来确定旧表中是否存在重复的“pk”值。

select pk 
from old_table_name
group by pk
having count() > 1;

您可能会考虑尝试使用 INSERT INTO 手动迁移数据。 。 。选择 。 。 . 如果失败，请添加 WHERE 子句来减小集合的大小，直到隔离出错误数据。

Answer 2

我发现使用 sqlite 排除外键约束错误很麻烦，特别是在大型数据集上。然而，以下方法有助于识别违规关系。

1. 禁用外键检查：PRAGMAforeign_keys = 0;
2. 执行导致错误的语句 - 在我的例子中，它是具有 3 个不同外键关系的 70,000 行的 INSERT。
3. 重新启用外键检查：PRAGMAforeign_keys = 1;
4. 识别外键错误：PRAGMAforeign_key_check(table-name);

在我的例子中，它显示了13行无效的参考文献。

Answer 3

以防万一有人登陆此处寻找“约束失败”错误消息，请确保您的 Id 列的类型为 INTEGER，而不是 INTEGER (0, 15)代码>或其他东西。

背景

如果您的表有一个名为 Id 的列，类型为 INTEGER 并设置为主键，SQLite 会将其视为内置的别名。在 RowId 列中。该列的工作原理类似于自动增量列。就我而言，该列工作正常，直到某个表设计者（可能是 SQLite 人员为 Visual Studio 创建的表设计者）将列类型从 INTEGER 更改为 INTEGER (0, 15) 突然我的应用程序开始抛出约束失败异常。

Answer 4

只是为了让它更清楚：

确保您使用的数据类型正确，我在创建 tmy 表时使用的是 int 而不是 integer ，如下所示：

id int 主键不为 null< /code>

并且我在几个小时内陷入了约束问题......
只需确保在创建数据库时正确输入数据类型即可。

Answer 5

发生此错误的原因之一是也为唯一行插入了重复数据。检查所有唯一键和重复数据的键。

Answer 6

今天我遇到了类似的错误：检查约束失败：配置文件

首先我读了这里什么是约束。

约束是一个完整性，它定义了一些条件
在插入或插入时限制列包含真实数据
更新或删除。我们可以使用两种类型的约束，即
列级或表级约束。列级别约束
只能应用于表级别的特定列
约束可以应用于整个表。

然后发现我需要检查数据库是如何创建的，因为我使用了 sqlalchemy 和 sqlite3 方言，所以我必须检查表schema。模式将显示实际的数据库结构。

sqlite3 >>> .schema
CREATE TABLE profile (
    id INTEGER NOT NULL, 
    ...
    forum_copy_exist BOOLEAN DEFAULT 'false', 
    forum_deleted BOOLEAN DEFAULT 'true', 
    PRIMARY KEY (id), 
    UNIQUE (id), 
    UNIQUE (login_name), 
    UNIQUE (forum_name), 
    CHECK (email_verified IN (0, 1)), 
    UNIQUE (uid), 
    CHECK (forum_copy_exist IN (0, 1)), 
    CHECK (forum_deleted IN (0, 1))

所以在这里我发现布尔值CHECK总是0或1，并且我使用列默认值作为“false”，所以每次我没有forum_copy_exist或forum_deleted值时，都会插入false，并且因为false是无效值，所以它会抛出错误，并且没有没有插入行。

因此更改数据库默认值是：

forum_copy_exist BOOLEAN DEFAULT '0', 
forum_deleted BOOLEAN DEFAULT '0',

解决了问题。

在 postgresql 中，我认为 false 是有效值。所以这取决于数据库模式是如何创建的。

希望这将来能对其他人有所帮助。

Answer 7

我在引用表上使用INTEGER作为INT，并在引用表中使用INTEGER作为外键。问题是你需要在这两个地方使用 INTEGER 。保持一致。因此在这两种情况下使用 INTEGER 解决了我的问题。 现在它运行起来就像魔法一样。