SQLite 3中空表的奇怪外键行为 [英] Strange foreign key behavior on empty tables in SQLite 3

问题描述

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

I have SQLite 3 with the following setup (simplified):

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 引用:

The table above is being referenced by 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引用:

The table above is being referenced by table 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));

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

The table child is the one I'm trying to delete from.

此时，整个数据库是空的，并且有pragma foreign_keys=ON".

At this point, the whole database is empty, and has "pragma foreign_keys=ON".

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

While testing a script to clear the database I encountered an error while deleting from the empty table Child having a foreign key to the (also empty) table Parent.

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

When I issue the command delete from child (while is already empty), SQLite3 comes back with the error message "foreign keys mismatch".

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

This is the vital part of the delete script:

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

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

I saw some posts here about temporarily disabling foreign key support, but that does not make sense to me. That makes the whole process of implementing a foreign key relationship unnecessary.

推荐答案

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

The documentation (hidden in the source code) says:

外键约束要求父项中的键列table 共同受 UNIQUE 或 PRIMARY KEY 约束.[…]如果找不到所需的索引，可能是因为:

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. 指定的父键列不存在，或
2. 命名的父键列确实存在，但不受UNIQUE 或 PRIMARY KEY 约束，或
3. 没有明确提供父键列作为外键定义，并且父表没有主键，或
4. 没有明确提供父键列作为外键定义，以及父表的 PRIMARY KEY由与子键不同数量的列组成子表.

然后……[引发]外键不匹配"错误.

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;
> 

这篇关于SQLite 3中空表的奇怪外键行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！