sql - SQLite 3中空表的奇怪外键行为

Question

我的 SQLite 3 具有以下设置(简化):

create table Location(LocationId integer not null,
                      LocationCode text not null, 
                      primary key(LocationId),
                      unique(LocationCode));

上表由部门引用:

create table Department(DepartmentId integer not null,
                        LocationId integer not null,
                        DepartmentCode text not null,
                        primary key(LocationId, DepartmentCode),
                        foreign key(LocationId) references Location(LocationId));

上表被 Child 引用:

create table Event(EventId integer not null,
                   LocationId integer not null,
                   unique(LocationId, EventDate),
                   primary key(eventId),
                   foreign key(LocationId) references Location(LocationId));

上表引用表位置:

create table Parent(ParentId integer not null,
                    EmailAddress text not null,
                    primary key(ParentId),
                    unique(EmailAddress));

上表被表 Child 引用:

create table Child(ChildId integer not null,
                   ParentId integer not null,
                   ChildCode text not null,
                   DepartmentId integer not null,
                   primary key(ChildId, ParentId),
                   foreign key(ParentId) references Parent(ParentId),
                   foreign key(DepartmentId) references Department(DepartmentId));

表子是我要从中删除的那个。

此时，整个数据库是空的，并且有“pragma foreign_keys=ON”。

在测试清除数据库的脚本时，我在从空表中删除时遇到了错误 Child对(也是空的)表有一个外键 Parent .

当我发出命令 delete from child (虽然已经为空)，SQLite3 返回错误消息“外键不匹配”。

这是删除脚本的重要部分:

delete from Child;
delete from Parent;
delete from Event;
delete from Department;
delete from Location;

我在这里看到了一些关于暂时禁用外键支持的帖子，但这对我来说没有意义。
这使得实现外键关系的整个过程变得不必要。

Answer 1

文档(隐藏在源代码中)说:

A foreign key constraint requires that the key columns in the parent table are collectively subject to a UNIQUE or PRIMARY KEY constraint. […] If the required index cannot be found, either because:

1. The named parent key columns do not exist, or
2. The named parent key columns do exist, but are not subject to a UNIQUE or PRIMARY KEY constraint, or
3. No parent key columns were provided explicitly as part of the foreign key definition, and the parent table does not have a PRIMARY KEY, or
4. No parent key columns were provided explicitly as part of the foreign key definition, and the PRIMARY KEY of the parent table consists of a a different number of columns to the child key in the child table.

then … a "foreign key mismatch" error [is raised].

> DELETE FROM Child;
Error: foreign key mismatch
> CREATE UNIQUE INDEX di ON Department(DepartmentId);
> DELETE FROM Child;
>