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

问题描述

我有SQLite 3，具有以下设置（简化）：

pre $ create table Location（LocationId integer not null，
LocationCode文本不为空，
主键（LocationId），
unique（LocationCode））;

上面的表格被Department引用：

（DepartmentId integer not null，
LocationId integer not null，
DepartmentCode text not null，
primary key（LocationId，DepartmentCode） ，
外键（LocationId）引用Location（LocationId））;

上表由Child引用：

  create table Event（EventId integer not null，
 LocationId integer not null，
 unique（LocationId，EventDate），
 primary key（eventId） ，
外键（LocationId）引用Location（LocationId））; 
  

上表引用表位置：

<$ （ParentId整数不为空，
EmailAddress文本不为空，
主键（ParentId），
唯一（EmailAddress））; pre>


上表由表Child引用：

  create table Child（ChildId integer not null，
 ParentId integer not null，
 ChildCode text not null，
 DepartmentId integer not null，
主键（ChildId，ParentId），
外键（ParentId）引用Parent（ParentId），
外键（DepartmentId）引用Department（DepartmentId））; 
  

表格中的孩子是我想要删除的孩子。

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

在测试脚本以清除数据库从空表 Child 删除（同时为空）父的外键时遇到错误。

当我发出命令从子删除（而已经是空的）时，SQLite3返回错误信息外键不匹配。

这是删除脚本的重要组成部分：

 从子项删除; 
从父项中删除; 
从Event中删除; 
从部门删除; 
从位置删除; 
  

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

解决方案

文档（隐藏在源代码）表示：
$ b

外键约束要求父
表中的键列统一使用UNIQUE或PRIMARY KEY约束。
[...]
如果找不到必需的索引，可能是因为：

1. 不存在，或
   
2. 命名的父键列确实存在，但不受
   UNIQUE或PRIMARY KEY约束，或
   
3. 没有父键列作为
   外键定义的一部分显式提供，并且父表没有
   PRIMARY KEY，或者
   
4. 没有父键列作为
   外键定义的一部分显式提供，并且父表
   的PRIMARY KEY由
   子表中的子键的不同数量的列组成。 li>
   

然后...一个外键不匹配错误[引发]

 > DELETE FROM Child; 
错误：外键不匹配
> CREATE UNIQUE INDEX di ON Department（DepartmentId）; 
> DELETE FROM Child; 
> 
  

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

The table above is being referenced by Department:

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

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

The table above references table Location:

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

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.

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

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.

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:

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

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