时间戳

首页 > 解决方案 > SQlite 允许自动增量创建引用另一个表的主键

sqlite - SQlite 允许自动增量创建引用另一个表的主键

问题描述

我有这两个表通过类表继承模式实现了一些继承关系：

pragma foreign_keys = ON;
create table foo(foo_id integer primary key);
create table bar(foo_id integer primary key references foo(foo_id));

让我们填充foo：

insert into foo values (1), (3), (4);

现在我可以3插入bar：

insert into bar values(3);  -- no error

我无法插入2：

insert into bar values(2);  -- Error: FOREIGN KEY constraint failed

然而，让我吃惊的是NULL值可以用来生成新的键：

insert into bar values(NULL); -- OK, 4 inserted
insert into bar values(NULL); -- FOREIGN KEY constraint failed

这种行为似乎很奇怪。当我在 MySQL 中尝试同样的事情时，我收到了一个

ERROR 1048 (23000): Column 'foo_id' cannot be null

这是我所期望的。

bar在使用子查询插入新行时，我发现这种行为特别危险：

insert into bar (foo_id) values ((select foo_if from foo where ...))

bar当 in 中没有匹配项时，最终可能会默默地插入随机行foo，而不是返回错误。

这种行为是否符合 SQL 标准，在什么情况下它是有用的，更重要的是，有没有一种方法可以改变这种行为以匹配 MySQL 的？

编辑

下面以一种可能更引人注目（也更可怕）的方式说明了这个问题：

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);
-- uh-oh, Jack the Ripper is now part of my family

标签： sqlite

解决方案

所以我找到了这个问题的答案，这对于不熟悉 SQLite 的人来说可能有点奇怪。

事实证明，integer primary key如果未给定值，则声明的列会自动填充未使用的整数。

在 SQLite 中解决这个问题的一个有点神秘的方法是使用声明列int primary key来代替，这可以防止隐式自动处理。

然而，我们还没有结束，因为 SQLite 的另一个独特行为是允许NULL主键——在我们上面的示例中，insert into bar values (NULL) 将插入一个带有NULL主键的新行。

要明确禁止主键具有NULL值，必须将其声明为非空：

pragma foreign_keys = ON;

create table foo(foo_id integer primary key);
create table bar(foo_id int primary key not null references foo(foo_id));

insert into foo values (1), (3), (4);

insert into bar values (3);
insert into bar values (2);  -- Error: FOREIGN KEY constraint failed
insert into bar values (NULL);  -- Error: NOT NULL constraint failed: bar.foo_id

这可能是在 SQLite 中默认声明指向外部键的主键的方式。

推荐阅读