sqlite - 指示一对多表中的 "canonical"记录

Question

想象一下，我们有一张国家表和一张城市表。一个国家当然可以有很多城市，但一个城市只能在一个国家，因此一对多的关系具有直观的意义:

countries
| id | name    |
|  1 | Lorwick |
|  2 | Belmead |

cities
| id | country | name        |
|  1 |       1 | Marblecrest |
|  2 |       1 | Westacre    |
|  3 |       2 | Belcoast    |
|  4 |       1 | Rosemarsh   |
|  5 |       2 | Vertston    |

但是除了我们的一对多关系之外，我们还想描述国家首都的一对一关系。如果这很重要，假设首都可能会相当有规律地变化，因此城市会随意出现和消失，并且城市可能会切换国家。关键是，这个数据是不稳定的。

我看到了几个选项:

添加一个 int 列 capital至countries不能为空。优点:总是只有一个城市；缺点:与城市无关，没有强制城市在乡村，或者它甚至存在。

添加 bool 列 capital至cities ，如果为真，则表明该城市是相关国家的首都。 Pro:直接与相关城市相关联，没有重复的列表示层次结构；缺点:很确定这是一个糟糕的正常化，因为没有什么可以阻止给定国家的零或多个“资本”。

创建一个附加表capitals带列 country和 city以及对两列(或至少在 city 上)的唯一约束。亲:感觉更干净，轻松加入 countries或 cities ;缺点:仍然不能确保城市在国内，或者两者都存在。

表示这种关系的最规范和/或最佳方式是什么？有什么办法可以确保每个国家都有一个确实存在并位于该国境内的资本？我想这是不可能的，在这种情况下，我怎样才能最好地减少我的客户端代码的问题？

我目前正在使用 SQLite，但无论底层数据库如何，我都对通用答案感兴趣。

我做了一点挖掘，发现 Indicating primary/default record in database但我认为这并不能真正回答我的问题。

PS:没有首都也没那么糟糕(可能没有城市!)，但如果有多个就糟糕了。

Answer 1

我认为“每个国家只有一个首都”的要求与“城市随意出现和消失”的要求是矛盾的。如果一个城市可以消失，那么一个首都也可以消失。

您可以使用大写表上的外键约束来强制执行“每个国家/地区有 [零或] 一个实际上确实存在并位于该国家/地区内的资本”的约束。

create table capitals (
  country_id integer primary key,
  city_id integer not null,
  foreign key (country_id, city_id) references cities (country_id, city_id)
);

在该表中，主键约束保证每个国家不能有超过一个资本。外键约束保证您选择的资本存在于您选择的国家。在引用的表(“城市”表)中，您还需要对 {city_id, country_id} 的唯一约束；由于 {city_id} 在“cities”表中是唯一的，{city_id, country_id} 在该表中也必然是唯一的，所以这不是问题。

保证国家和首都之间的一对一关系(不是一对零或一的关系)的声明性“方式”是使用断言。但我不知道当前有任何支持 CREATE ASSERTION 的 SQL dbms。这迫使我们依赖其中的一项或多项:

触发器和可能的延迟约束，

应用程序代码，或

行政程序。

(最初，您必须在单个事务中在三个表“国家”、“城市”和“首都”中输入一行以满足所有约束。我认为您需要延迟约束，但我今天还没喝咖啡。)