跳转到内容

结构化查询语言/外键

Edit links
来自维基教科书，自由的教科书
数据类型 结构化查询语言
外键 		修改表



外键 (FK) 定义了从一个表（子表）到另一个表（父表）的定向引用。只要两个表中涉及的列包含相同的值，该引用就一直有效。它将子表中的一行与父表中的一行相关联 - 父表中的一行可以与子表中的多行相关联。

例如：您可能拥有包含列id 的表department 和包含列dept_id 的表employee 。如果您想将一名员工分配到某个特定的部门，您可以在其dept_id 列中存储部门 ID。这在任何情况下都可以进行，无论是否定义外键。但在这种情况下，人们通常还有两个额外的要求：首先，员工只能分配到确实存在的部门。其次，只要员工被分配到某个特定的部门，就应该无法删除该部门。外键的主要目的就是保证这两个要求。

换句话说：外键保证不会出现孤儿。

外键与联结

[编辑 | 编辑源代码]

在关系型数据库管理系统中，相同的值用于将不同表（有时甚至是同一个表）的行链接在一起。由于这种链接是基于值而不是任何链接或特殊引用，所以它没有方向。一般来说，我们称这种技术为联结。外键具有非常类似的概念，因为它们也使用相同的值将行链接在一起。但它们也存在重要的区别。

  • 外键具有方向。了解两个受影响的表中的哪一个是子表，哪一个是父表很重要。
  • 联结必须在所有感兴趣的 DML 语句中表达（视图除外）。相反，外键是表定义的一部分。所有 DML 命令都会考虑到它们，而无需在 DML 语句中表达它们。

语法

[编辑 | 编辑源代码]
-- As part of CREATE TABLE command
CREATE TABLE <table_name> (
  ...
  CONSTRAINT <constraint_name> FOREIGN KEY (<column_name>) REFERENCES <parent_table_name> (<other_column_name>)
);

-- As part of ALTER TABLE command
ALTER TABLE <table_name> ADD  CONSTRAINT <constraint_name> ... ; -- same as above
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;      -- throw the definition away


规则

  • FK 约束可以在表定义期间（CREATE TABLE）或之后（ALTER TABLE）定义。在本页中，我们重点介绍 CREATE TABLE 语句。ALTER TABLE 语句的语法非常类似。
  • FK 约束属于子表定义。
  • 尽管存在 FK 约束，但子表中的行可能不属于任何父行。如果子行的列值为 NULL，就会发生这种情况。如果要避免这种情况，请将该列定义为“NOT NULL”。
  • 尽管 FK 约束属于子表，但它们对父表也有影响，因此如果在子表中存在与该父行匹配的行，则无法删除父表中的行。
  • 指定的父表必须存在。
  • 指定的父表列必须是其主键或唯一的列。
  • 在一个 FK 约束中使用同一个表作为父表和子表是完全可以的，参见：练习。
  • 一个表可以受许多 FK 约束的约束。

例子

[编辑 | 编辑源代码]

该示例定义了department 和employee 表。employee 的外键定义声明department 是employee 的父表。

--
-- The parent table: DEPARTMENT
CREATE TABLE department (
  id         DECIMAL,
  dept_no    CHAR(10),
  dept_name  VARCHAR(100),
  CONSTRAINT dept_pk PRIMARY KEY (id)
);

-- The child table: EMPLOYEE
CREATE TABLE employee (
  id         DECIMAL,
  emp_name   VARCHAR(100),
  dept_id    DECIMAL,
  CONSTRAINT emp_pk PRIMARY KEY (id),
  CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES department(id)
);

-- This INSERT will fail because currently there is no department with id 10.
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (1, 'Mike Baker', 10);
COMMIT;

-- It's necessary to store the department first.
INSERT INTO department (id, dept_no, dept_name) VALUES (10,'D10', 'E-Bike Development');
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (1, 'Mike Baker', 10);
COMMIT;

-- The department may have a lot of employees
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (2, 'Elenore McNeal', 10);
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (3, 'Ted Walker', 10);
COMMIT;

-- This DELETE will fail because currently there are employees within the department.
DELETE FROM department WHERE dept_name = 'E-Bike Development';
COMMIT;

这种建模方式允许表示分层树结构。一个或多个子节点（行）属于一个父节点（行）。在数据库管理系统的上下文中，这种关联称为 1:m 关系。

n:m 关系

[编辑 | 编辑源代码]

在现实世界中，除了 1:m 关系之外，还有更多类型的关联。通常，存在所谓的 n:m 关系，其中对象（行）属于多个其他对象（行）。因此，父表/子表的含义就会消失。在我们示例数据库 中，有一个hobby 表，另一个person 表。一个人可能拥有多个爱好。同时，多个人可能拥有相同的爱好。这可以通过在两个原始表之间创建一个第三个表来设计。第三个表保存第一个表和第二个表的 ID。这样就可以决定哪个人拥有哪个爱好。

实现这种 n:m 情况的技术与上一章中 1:m 关联中所示相同，只是它被使用了两次。我们定义了两个外键，它们从“中间表”开始，并引用了另外两个表。从技术角度来说，我们可以说“中间表”是两个父表person 和hobby 的子表。person 和hobby 处于相同的逻辑级别。

--
CREATE TABLE t1 (
  id               DECIMAL,
  name             VARCHAR(50),
  -- ...
  CONSTRAINT t1_pk               PRIMARY KEY (id)
);
CREATE TABLE t2 (
  id               DECIMAL,
  name             VARCHAR(50),
  -- ...
  CONSTRAINT t2_pk               PRIMARY KEY (id)
);
CREATE TABLE t1_t2 (
  id               DECIMAL,
  t1_id            DECIMAL,
  t2_id            DECIMAL,
  CONSTRAINT t1_t2_pk            PRIMARY KEY (id),       -- also this table should have its own Primary Key
  CONSTRAINT t1_t2_unique        UNIQUE (t1_id, t2_id),  -- every link should occur only once
  CONSTRAINT t1_t2_fk_1          FOREIGN KEY (t1_id) REFERENCES t1(id),
  CONSTRAINT t1_t2_fk_2          FOREIGN KEY (t2_id) REFERENCES t2(id)
);

ON DELETE / ON UPDATE

[编辑 | 编辑源代码]

到目前为止，我们假设如果在子表中存在与该父行匹配的行，则无法删除父表中的行。这是默认行为，但总的来说，SQL 标准定义了五种选项，以各种方式处理这种父/子情况。这些选项扩展了约束定义。它们是

  • ON DELETE CASCADE：如果删除父表中的一行，则删除引用表中所有匹配的行。
  • ON DELETE SET NULL：如果删除父表中的一行，则将子表中所有匹配行中的所有引用列设置为 NULL。
  • ON DELETE SET DEFAULT：如果删除父表中的一行，则将子表中所有匹配行中的所有引用列设置为该列的默认值。
  • ON DELETE RESTRICT：如果在子表中存在与该行匹配的行，则禁止删除父表中的一行。检查发生的时刻可以延迟到 COMMIT。
  • ON DELETE NO ACTION （默认）：如果在子表中存在与该行匹配的行，则禁止删除父表中的一行。这在所有情况下都适用，即使检查被延迟（参见下一章）。

与 ON DELETE 选项类似，还有一个 ON UPDATE 选项。它为在父表中更改由子表列引用的列的情况定义了相同的五种选项。

  • ON UPDATE CASCADE：对父表中引用列的任何更改都会导致子表中匹配行中的对应引用列发生相同的更改。
  • ON UPDATE SET NULL：对父表中引用列的任何更改都会导致子表中匹配行中的对应引用列被设置为 null。
  • ON UPDATE SET DEFAULT：对引用表中引用列的任何更改都会导致引用表中匹配行中的对应引用列被设置为其默认值。
  • ON UPDATE RESTRICT：如果在子表中存在与该行匹配的行，则禁止更改父表中的一行。检查发生的时刻可以延迟到 COMMIT。
  • ON UPDATE NO ACTION （默认）：如果在子表中存在与该行匹配的行，则禁止更改父表中的一行。这在所有情况下都适用，即使检查被延迟（参见下一章）。

如果未指定 ON DELETE 或 ON UPDATE，则会执行默认操作 NO ACTION。在某些系统中，NO ACTION 是以 RESTRICT 选项的意义实现的。

一个例子

--
CREATE TABLE t1_t2 (
  ...
  CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
  ...
);

提示 1：更新主键的概念存在争议。

提示 2：并非所有 DBMS 都支持所有选项。

立即/延迟

[编辑 | 编辑源代码]

还有一个额外的选项可以决定何时评估外键定义。默认行为是在每个 UPDATE 和 DELETE 命令执行时进行检查。第二个可能性是将检查延迟到事务结束时，即 COMMIT 命令。延迟的目的是让应用程序能够在子表之前修改父表（如果它们使用 Hibernate，这可能会有所帮助）。

要定义此选项，约束定义必须通过关键字 [NOT] DEFERRABLE 扩展，这些关键字在 INITIALLY IMMEDIATE（默认）或 INITIALLY DEFERRED 之前或之后添加，以指定 CREATE TABLE 时刻后的初始状态。

--
CREATE TABLE t1_t2 (
  ...
  CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id)
                        ON UPDATE CASCADE  DEFERRABLE INITIALLY IMMEDIATE
                        ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
  ...
);

提示：MySQL 不支持 DEFERRABLE 选项，但可以通过“SET foreign_key_checks = 0/1;”动态地激活和停用外键检查。

鸡和蛋的问题

[编辑 | 编辑源代码]

有时应用程序会遇到循环依赖：表 A 包含对表 B 的引用，反之亦然，例如：表 team 包含列 id、team_name 和 team_leader（这是一个指向球员的 ID），而表 player 包含列 id、player_name 和 team_id。

--
CREATE TABLE team (
  id               DECIMAL,
  team_name        VARCHAR(50),
  team_leader      DECIMAL,      -- ID of a player
  CONSTRAINT team_pk             PRIMARY KEY (id)
);

CREATE TABLE player (
  id               DECIMAL,
  player_name      VARCHAR(50),
  team_id          DECIMAL,
  CONSTRAINT player_pk           PRIMARY KEY (id)
);

ALTER TABLE team   ADD CONSTRAINT team_fk   FOREIGN KEY (team_leader) REFERENCES player(id);
ALTER TABLE player ADD CONSTRAINT player_fk FOREIGN KEY (team_id)     REFERENCES team(id);

到目前为止，情况都不好。当插入第一行团队数据时，球员数据会丢失。当先插入球员数据时，团队数据会丢失。

正如我们上面所见，存在一个 DEFER 选项。使用此选项，必须定义 FK 约束，以使它们不会立即在 INSERT 命令执行时进行评估。它们应该在所有 INSERT 操作完成后在 COMMIT 时点进行评估。

-- Throw the above definitions away ...
ALTER TABLE team   DROP CONSTRAINT team_fk;
ALTER TABLE player DROP CONSTRAINT player_fk;
-- ... and use DEFERRABLE
ALTER TABLE team   ADD  CONSTRAINT team_fk   
                        FOREIGN KEY (team_leader) REFERENCES player(id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE player ADD  CONSTRAINT player_fk
                        FOREIGN KEY (team_id)     REFERENCES team(id)   DEFERRABLE INITIALLY DEFERRED;

现在我们可以以任何顺序插入数据（不要忘记停用 AUTOCOMMIT）。

--
INSERT INTO team   (id, team_name,   team_leader) VALUES (1, 'Wild Tigers',  1);
INSERT INTO player (id, player_name, team_id)     VALUES (1, 'Johnny Crash', 1);
-- No checking of Foreign Keys up to here
COMMIT;   -- Commit includes the check of Foreign Keys

DROP TABLE / TRUNCATE TABLE

[编辑 | 编辑源代码]

外键对 DROP TABLE 和 TRUNCATE TABLE 命令有影响。只要外键引用父表，就不能删除（删除结构和数据）或截断（仅删除数据）该表。即使没有实际行引用父表中的任何行，外键的存在就足以拒绝 DROP 和 TRUNCATE。

要使用 DROP 或 TRUNCATE，必须先删除约束。

提示：一些实现提供了 DISABLE/ENABLE 命令，用于临时停用约束。


练习

[编辑 | 编辑源代码]

FK 约束的父表是否可能包含 1 行，而子表为空？

点击查看解决方案
Yes. Parents without children are absolutely normal.

FK 约束的子表是否可能包含 1 行，而父表为空？

点击查看解决方案
Yes. Although the main purpose of FK-constraints is the prevention of children without parents (orphans), this situation may occur.
If the column of the child row contains the NULL value, this row relates to no parent row
because 'null = <any value>' evaluates always to UNKNOWN and never to TRUE, even if that <any value> is the NULL value.

创建一个名为 genealogy 的表，用于存储有关人员及其祖先的信息。列为：id、first_name、last_name、birth_name、father_id、mother_id。

点击查看解决方案
CREATE TABLE genealogy (
  id             DECIMAL                           PRIMARY KEY,
  first_name     VARCHAR(100),
  last_name      VARCHAR(100),
  birth_name     VARCHAR(100),
  father_id      DECIMAL,
  mother_id      DECIMAL
);

通过两个 FK 约束扩展 genealogy 表，使列“father_id”和“mother_id”引用该表的其他行。

点击查看解决方案
ALTER TABLE genealogy ADD CONSTRAINT gen_fk_1 FOREIGN KEY (father_id) REFERENCES genealogy(id);
ALTER TABLE genealogy ADD CONSTRAINT gen_fk_2 FOREIGN KEY (mother_id) REFERENCES genealogy(id);

将一些数据插入“genealogy”，例如：您个人家庭的数据。

点击查看解决方案
-- For the first rows store NULL in 'father_id' and 'mother_id'!
INSERT INTO genealogy (id, first_name, last_name, birth_name, father_id, mother_id)
               VALUES (1,  'Mike',     'Miller',  'Miller',   null,      null);
INSERT INTO genealogy (id, first_name, last_name, birth_name, father_id, mother_id)
               VALUES (2,  'Eve',      'Miller',  'Summer',   null,      null);
INSERT INTO genealogy (id, first_name, last_name, birth_name, father_id, mother_id)
               VALUES (3,  'Marry',    'Dylan',   'Miller',   1,      2);
INSERT INTO genealogy (id, first_name, last_name, birth_name, father_id, mother_id)
               VALUES (4,  'Henry',    'Dylan',   'Dylan',    null,      3);
COMMIT;


数据类型 结构化查询语言
外键 		修改表
检索自 "https://wikibooks.cn/w/index.php?title=Structured_Query_Language/Foreign_Key&oldid=3698910"
华夏公益教科书