如何使用非唯一外键强制数据库完整性？

Question

我想要一个数据库表来保存具有修订历史记录的数据（例如维基百科上的页面）。我认为一个好主意是使用两列来标识行：(name, version)。因此，示例表如下所示：

TABLE PERSONS:
    id:      int,
    name:    varchar(30),
    version: int,
    ... // some data assigned to that person.

因此，如果用户想要更新人员的数据，他们不会进行更新 - 相反，他们会创建一个具有相同 name 但不同 的新 PERSONS 行>版本值。向用户显示的数据（对于给定的名称）是具有最高版本的数据。

我有第二个表，例如 DOGS，它引用 PERSONS 表中的人员：

TABLE DOGS:
    id:         int,
    name:       varchar(30),
    owner_name: varchar(30),
    ...

显然，owner_name 是对 PERSONS.name 的引用，但我无法将其声明为外国键（在 MS SQL Server 中），因为 PERSONS.name 不是唯一的！

问题：那么，在 MS SQL Server 2008 中，我应该如何确保数据库完整性（即，对于每个 DOG，在 PERSONS 中至少存在一行，使其 PERSON.name == DOG。所有者名称）？

我正在寻找最优雅的解决方案——我知道我可以在 PERSONS 表上使用触发器，但这并不像我希望的那样具有声明性和优雅性。有什么想法吗？

---

附加信息

上面的设计有以下优点，如果我需要，我可以“记住”一个人当前的id（或（名称，版本） 对），我确信该行中的数据永远不会更改。这很重要，例如，如果我将这个人的数据作为文档的一部分，然后打印出来，并且在 5 年后，有人可能想打印一份完全相同的副本（例如，使用与今天相同的数据），那么这将非常容易供他们做。

也许您可以想到一种完全不同的设计来实现相同的目的，并且可以更容易地强制执行其完整性（最好使用外键或其他约束）？

---

编辑：感谢迈克尔·加图索的回答，我发现了描述这种关系的另一种方式。有两种解决方案，我将其作为答案发布。请投票选出您更喜欢哪一位。

Answer 1

我参与的一个项目解决了类似的问题。这是一个生物记录数据库，随着新研究提高了对分类学的理解，物种名称可能会随着时间的推移而改变。

然而，旧记录需要保持与原始物种名称的相关性。它变得很复杂，但基本的解决方案是拥有一个仅包含所有唯一物种名称的 NAME 表、一个代表实际物种的物种表和一个将两者链接在一起的 NAME_VERSION 表。在任何时候都会有一个首选名称（即该物种当前接受的学名），它是 name_version 中保存的布尔字段。

在您的示例中，这将转换为“详细信息”表（detailsid、otherdetails 列）、一个名为“DetailsVersion”（detailsid、personid）的链接表和一个“人员表”（personid、不变数据）。将狗与人联系起来。

Answer 2

人员

ID (int)，
姓名，
......
activeVersion（这将是来自 personVersionInfo 的 UID）

注意：上表中每个人都有 1 行。将拥有创建该人的原始信息。

PersonVersionInfo

UID（识别人员+版本的唯一标识符），
id（整数），
姓名，
......
versionId（将为每个人生成）

Dogs

DogID，
狗名
......

PersonsWithDogs

UID，
DogID

编辑：您必须加入 PersonWithDogs、PersionVersionInfo、Dogs 才能了解完整情况（截至今天）。这种结构将帮助您将狗链接到所有者（具有特定版本）。

如果人的信息发生变化，并且您希望获得与狗相关的最新信息，则必须更新 PersonWithDogs 表以获得给定狗所需的（人的）UID。

您可以设置一些限制，例如 DogID 在 PersonWithDogs 中应该是唯一的。
而在这个结构中，一个UID（人）可以有很多个Dog。

您的场景（可以更改/限制等）将有助于更好地设计模式。

Answer 3

感谢迈克尔·加图索的回答，我发现了描述这种关系的另一种方式。有两个解决方案，这是第一个。请投票选出您更喜欢哪一位。

解决方案 1

在 PERSONS 表中，我们仅保留姓名（唯一标识符）和指向当前人员数据的链接：

TABLE PERSONS:
    name:            varchar(30),
    current_data_id: int

我们创建一个新表 PERSONS_DATA，其中包含所有数据历史记录对于那个人：

TABLE PERSONS_DATA:
    id:      int
    version: int (auto-generated)
    ... // some data, like address, etc.

DOGS 表保持不变，它仍然指向一个人的名字（FK 到 PERSONS 表）。

优点：对于每只狗，至少存在一个 PERSONS_DATA 行，其中包含其所有者的数据（这就是我想要的）

缺点：如果要更改一个人的数据，则必须：

1. 添加一个新的 PERSONS_DATA
2. 行为此更新 PERSONS 条目person 指向新的 PERSONS_DATA 行。

Answer 4

感谢迈克尔·加图索的回答，我发现了描述这种关系的另一种方式。有两种解决方案，这是其中的第二个。请投票选出您更喜欢哪一位。

解决方案 2

在 PERSONS 表中，我们仅保留姓名（唯一标识符）和指向第一个（不是当前！）人员数据的链接：

TABLE PERSONS:
    name:            varchar(30),
    first_data_id: int

我们创建一个新表 PERSONS_DATA ，包含该人的所有数据历史记录：

TABLE PERSONS_DATA:
    id:      int
    name:    varchar(30)
    version: int (auto-generated)
    ... // some data, like address, etc.

DOGS 表保持不变，它仍然指向一个人的姓名（FK 到 PERSONS 表）。

优点：

• 对于每只狗，至少存在一个 PERSONS_DATA 行，其中包含其所有者的数据（这就是我想要的），
• 如果我想更改一个人的数据，我不必更新 PERSONS 行，只需添加一个新的 PERSONS_DATA行

缺点：要检索当前人员的数据，我必须：

• 选择具有给定名称和最高版本的 PERSONS_DATA （可能很昂贵）
• 选择具有特殊版本的 PERSONS_DATA，例如“-1”，但随后我必须分别更新两个 PERSONS_DATA 行当我添加新的 PERSONS_DATA 时，在这个解决方案中我想避免更新 2 行......

你觉得怎么样？

Answer 5

在您的父表中，创建对（id，版本）的唯一约束。将 version 列添加到您的子表，并使用检查约束来确保它始终为 0。使用 FK 约束将 (parentid, version) 映射到您的父表。

Answer 6

或者，您可以为具有历史价值的数据维护一个人员历史表。通过这种方式，您可以保持“人和狗”表格的整洁和参考的简单，同时还可以访问历史上有趣的信息。

Answer 7

好的，第一件事是您需要规范化您的表。谷歌“数据库规范化”，你会找到大量的阅读材料。 PERSONS 表尤其需要注意。

第二件事是，当您创建外键引用时，99.999% 的情况下您想要引用 ID（数字）值。即，[DOGS].[owner] 应该是对 [PERSONS].[id] 的引用。

编辑：添加示例架构（原谅松散的语法）。我假设每只狗都只有一个主人。这是实现人员历史记录的一种方式。所有列都不为空。

Persons Table:
int Id
varchar(30) name
...

PersonHistory Table:
int Id
int PersonId (foreign key to Persons.Id)
int Version (auto-increment)
varchar(30) name
...

Dogs Table:
int Id
int OwnerId (foreign key to Persons.Id)
varchar(30) name
...

最新版本的数据将直接存储在 Persons 表中，较旧的数据存储在 PersonH​​istory 表中。

Answer 8

我会使用关联表将多个版本链接到一个 pk。