主页 / dba / 问题 / 345047
Accepted
user209974
Asked: 2025-02-02 01:38:05 +0800 CST

这怎么会是 sqlite3 的一个特性呢？外键关系出错了

  • 772

以下面的代码为例：

pragma foreign_keys = ON;

create table people(people_id integer primary key, name text not null);
insert into people (name) values ("Mom"), ("Jack the Ripper");

create table family_member(people_id integer primary key references people(people_id));

insert into family_member values ((select people_id from people where name = "Mom"));
insert into family_member values ((select people_id from people where name = "Dad"));  -- silent error here

select name from family_member inner join people using (people_id);
-- Jack the Ripper is part of the family

这在 sqlite 中怎么会是合法的？甚至没有发出警告。这是出于好的理由，还是出于遗留原因，文档中是否有我尚未找到的内容？对我来说，这令人担忧，我相信没有其他 SQL DB 会这样做。

sqlite

1 个回答

  1. Best Answer

    INTEGER PRIMARY KEYSQLite 中的列相当奇怪。它们默认表示为ROWID或AUTOINCREMENT，或者在其他语言中表示为SERIAL或IDENTITY。

    来自文档：

    除下面提到的一个例外情况外，如果 rowid 表的主键由单个列组成，并且该列的声明类型为“INTEGER”（大小写任意混合），则该列将成为 rowid 的别名。...
    INSERT
    语句可以提供一个值作为插入的每一行的 rowid。与普通 SQLite 列不同，整数主键或 rowid 列必须包含整数值。...
    如果
    INSERT 语句尝试将 NULL 值插入 rowid 或整数主键列，则系统会自动选择一个整数值作为 rowid。有关如何执行此操作的详细说明将单独提供。

    该链接文件指出：

    如果插入时未指定 ROWID，或者指定的 ROWID 值为 NULL，则会自动创建适当的 ROWID。通常的算法是为新创建的行指定一个 ROWID，该 ROWID 比插入前表中的最大 ROWID 大 1。

    现在，结合您对子查询的相当奇怪的使用，很清楚发生了什么。

    子查询单独执行时，不会返回任何行：

    select people_id from people where name = "Dad"

    当放置在标量子查询中时，“无行”将转换为NULL。因此它变成：

    insert into family_member values (NULL);

    然后引擎决定突然创建一个值，而该值恰好与外键中的现有值相匹配。

    我还注意到您没有指定要插入哪些列，这可能会在以后给您带来麻烦。

    正确的解决方案是在您的表上指定WITHOUT ROWID。这意味着引擎不会为该列生成值，因此插入将失败。您还需要明确指定约束NOT NULL，因为 SQLite 在大多数情况下不会默认添加它。

    create table family_member (
    people_id integer not null primary key references people(people_id)
) WITHOUT ROWID;

    您还应该将您的查询更改INSERT为更正常的INSERT...SELECT...没有子查询的查询，这将导致首先不插入任何行。

    insert into family_member (people_id)
select people_id
from people
where name = 'Mom';

insert into family_member (people_id)
select people_id
from people
where name = 'Dad';
    • 25

相关问题