5. 表之间的关系

5.1. 外键

关系型数据库是由通过关系相互关联的表组成的集合。我们以之前的表 [BIBLIO] 为例，其结构如下：

内容示例如下：

我们可能需要获取这些作品不同作者的信息，例如其 nom 和 prénom，以及其出生日期 nationalité。让我们创建这样一张表。右键单击 [DBBIBLIO / Tables]，然后选择 [New Table] 选项：

现在构建以下 [AUTEURS] 表：

`id`	表的主键——用于唯一标识一行
`nom`	作者姓名
`prénom`	作者的名字（如有）
`date_naissance`	出生日期
`nationalite`	其原籍国

表 [AUTEURS] 的内容可能如下：

让我们回到表 [BIBLIO] 及其内容：

在该表的 [AUTEUR] 字段中，不再需要填写作者姓名。更推荐填写其在 [AUTEURS] 表中的编号（ID）。因此，我们创建一个名为 [LIVRES] 的新表。创建该表时，我们将使用第3.14节中创建的脚本[biblio.sql]。我们使用工具[Script Executive, Ctrl-F12]加载该脚本：

我们将表创建脚本 BIBLIO 进行修改，使其与表 LIVRES 的脚本保持一致：

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     唯一约束 ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                                                           主键 ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

我们仅对变更部分进行说明：

第 4 行：表中的字段 [AUTEUR] 改为整数类型。该编号引用了先前构建的表 [AUTEURS] 中的某位作者。
第11-19行：作者姓名已被其作者编号取代。
第29行：约束的名称已更改。它以前名为[ UNQ1_BIBLIO ]，现在名为[ UNQ1_LIVRES ]。该名称可以是任意名称，但最好具有实际意义。此处未做此方面的努力。数据库中不同字段、不同表上的约束必须通过不同的名称加以区分。需要提醒的是，第29行的约束要求表中标题必须唯一。
第36行：更改主键约束的名称为ID。

执行此脚本。若执行成功，我们将获得如下新的表 [LIVRES]：

我们不禁要问，这次修改是否真的有所改善。事实上，表 [LIVRES] 中显示的是作者编号而非姓名。由于作者数量多达数千，一本书与其作者的关联似乎难以建立。所幸SQL语句能为我们提供帮助。它允许我们同时查询多个表。在此示例中，我们展示查询语句SQL，它能帮助我们获取图书馆藏书的书名及其对应的作者信息。使用编辑器 SQL (F12) 发出以下命令 SQL：

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

目前尚无法解释该 SQL 命令。我们将稍后对此进行说明。该查询的结果如下：

每本书都已正确地与作者及其相关信息关联起来。

总结一下我们刚才所做的工作：

我们有两张收集不同类型信息的表：
- AUTEURS表收集了关于作者的信息
- 表 LIVRES 收集图书馆购买的书籍信息
这两个表相互关联。一本书必然有一位作者，甚至可能有多位作者。此处未考虑这种情况。表 [LIVRES] 中的字段 [AUTEUR] 引用了表 [AUTEURS] 中的某一行。这被称为关系。

将表 [LIVRES] 与表 [AUTEURS] 关联的关系实际上是一种约束：表 [LIVRES] 中的每一行都必须拥有一个在表 [AUTEURS] 中存在的作者编号。如果 [LIVRES] 表中某行记录的作者编号在 [AUTEURS] 表中不存在，就会出现异常情况，导致无法查找到该书的作者。

SGBD 表能够确保该约束始终成立。为此，我们将向 [LIVRES] 表添加一个约束：

将表 [LIVRES] 中的字段 [AUTEUR] 与表 [AUTEURS] 中的字段 [ID] 关联的链接称为外键链接。在上述向导中，表 [LIVRES] 中的字段 [AUTEUR] 被称为“外键”或“foreign key”。定义外键意味着：表 [T1] 中列 [c1] 的值必须存在于表 [T2] 的列 [c2] 中。表 [c1] 的列 [c1] 被称为表 T1 针对表 [T2] 的列 [c2] 的“外键”。列 [c2] 通常是表 [T2] 的主键，但这并非强制要求。

我们将表 [LIVRES] 的外键 [AUTEUR] 定义为表 [AUTEURS] 的字段 [ID]，定义方式如下：

约束名称：任意
“外键”列，此处为表 [LIVRES] 中的列 [AUTEUR]
被外键引用的表。此处，表 [LIVRES] 中的列 [AUTEUR] 必须在表 [AUTEURS] 的列 [ID] 中具有对应值。因此，被引用的表是 [AUTEURS]。
外键引用的列。此处为表 [AUTEURS] 中的列 [ID]。

我们验证此约束：

如果一切正常，该约束将被接受：

这个新的外键约束会产生什么影响？使用编辑器 SQL（F12），尝试向表 LIVRES 中插入一行，其中作者编号不存在：

上述操作 [INSERT] 尝试插入一本作者编号（100）不存在的书。该查询执行失败。相关的错误消息指出，外键约束“FK_LIVRES_AUTEURS”被违反。这就是我们刚刚定义的那个约束。

5.2. 两张表之间的连接操作

仍在数据库 [DBBIBLIO]（或其他任意数据库）中，创建两个名为 TA 和 TB 的测试表，其定义如下：

表 TA

- ID：表 TA 的主键

- DATA：任意数据

表 TB

- ID：表 TB 的主键

- IDTA：表 TB 的外键，该外键引用表 TA 的列 ID。因此，表 TA 中 IDTA 列的某个值必须存在于表 TA 的 ID 列中

- VALEUR：任意数据

在编辑器 SQL（F12）中，我们将发出 SQL 命令，同时利用 TA 和 TB 这两张表。

SQL>select * from TA,  TB

命令 SQL 在关键字 FROM 之后，涉及两个表 TA 和 TB。操作 FROM TA, TB 将导致临时创建一个新表，其中表 TA 的每一行都将与表 TB 的每一行建立关联。因此，如果表 TA 有 NA 行，表 TB 有 NB 行，则生成的表将包含 NA × NB 行。上图截图即展示了这一情况。此外，每行都包含两张表的列。在 [SELECT col1, col2, ... FROM ...] 查询中指定的 coli 列即为需要保留的列。此处关键字 * 表示需要结果表中的所有列。有时会说，前一个查询 SQL 的结果表是表 TA 和 TB 的笛卡尔积。

在上文中，表 TA 的每一行都与表 TB 的每一行进行了配对。通常，我们希望将 TA 表中的一行，与其相关的 TB 表中的行进行关联。这种关系通常表现为外键约束。本例即属此类。对于表 TA 中的某一行，我们可以关联表 TB 中满足关系 TB.IDTA=TA.ID 的行。实现此查询有多种方法：

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

前面的 SQL 命令与前一个类似，但有两点不同：

笛卡尔积 TA × TB 的结果行，通过一个 WHERE 子句进行过滤，该子句将 WHERE 表中的一行，且仅筛选出满足关系 TB.IDTA=TA.ID 的 TB 表中的行
仅查询特定列时，使用语法 [T.col]，其中 T 表示表名，col 表示该表中列名。此语法可消除两张表存在同名列时可能产生的歧义。当不存在这种歧义时，可以使用语法 [col]，无需指定该列所属的表。

所得结果如下：

通过以下命令 SQL 也可获得相同结果：

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

术语 [inner join] 衍生出了“内部连接”这一名称，用于指代两张表之间的此类操作。我们还将看到“外部连接”的存在。在内部连接中，查询中表的顺序不会影响结果： FROM TA inner join TB 与 FROM TB inner join TA。

前面的查询 SQL 仅将 TA 表中被 TB 表中至少一行引用的行放入结果表中。因此，TA 表中的 [3, data3] 行不会出现在结果中，因为它未被 TB 表中的任何一行引用。如果需要获取 TA 表中的所有行（无论是否被 TB 表中的某行引用），则需对这两个表使用外部连接：

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA

这里是一个“左外连接”（left outer join）。要理解“FROM TA left outer join TB”这一术语，可以设想一个左侧为表 TA、右侧为表 TB 的连接。左侧表中的所有行都会出现在左外连接的结果中，即使那些未满足连接关系的行也是如此。这种连接关系不一定是外键约束，尽管这是最常见的情况。

按以下顺序：

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

在外部连接中，TB 表位于“左侧”。因此，结果中将包含 TB 表的所有行：

与内部连接不同，表的顺序在此起着关键作用。此外还存在右外连接：

FROM TA left outer join TB 等同于 FROM TB right outer join TA：表 TA 位于左侧
FROM TB 左外连接 TA 等同于 FROM TA 右外连接 TB：TB表位于左侧

既然已经了解了同时操作多个表的基础知识，我们就可以探讨数据库中更复杂的查询操作了。