什么是外键？(附SQL实例)外键是 "关系数据库 "中的 "关系"--它们帮助定义表之间的关系。它们允许开发人员在他们

外键是 "关系数据库 "中的 "关系"--它们帮助定义表之间的关系。它们允许开发人员在他们的数据库中保持参考完整性。外键还可以帮助终端用户防止错误，并提高从由索引外键链接的表中提取数据的任何操作的性能。

在技术层面上，外键是一种约束，它将一个表（table_1.column_a ）中的列与另一个表（table_2.column_b ）中的列联系起来，并确保只有在column_b 中已经存在相同的值时才能将该值添加到column_a 。

例如，一个客户订单表可能有一个user 列，该列有一个外键属性，将其链接到用户表中的user_id 列。这样，订单表中的每一行都可以与用户表中的一个特定用户相关联--如果没有一个有效的用户与之相关联，就没有订单可以进入系统。

外键在实践中是如何工作的？让我们实际一点，通过观察外键在一个简单的SQL数据库中的作用，来了解更多关于外键的信息。

在这篇文章中，我们将介绍。

主键和外键的区别
为什么外键在关系型数据库中很重要？
如何使用SQL创建带有外键的表
通过外键操作控制删除和更新如何影响多个表

让我们深入了解一下吧

我们的示例数据库

下面，我们设置了一个我们要使用的样本数据库。它代表了一个虚构的在线书店的销售数据库。我们可以看到有三个表。

users 包含关于在网站上注册的用户的数据
orders 包含通过该网站下的具体订单的数据
books 包含关于可供销售的书籍的数据

what is a foreign key

不用说，现实生活中的书店的数据库会比这个大得多，也复杂得多！但这个样本数据库将使我们更容易理解。但这个样本数据库将使我们更容易说明外键是如何工作的，而且这里适用的原则将以完全相同的方式适用于更大和更复杂的数据库。

(如果你想亲身体验一下这个数据库，请跳到文章末尾的说明，它可以帮助你在五分钟内将其设置在一个免费的CockroachCloud集群上）。

主键和外键之间有什么区别？

要了解外键是如何工作的，首先要了解主键。

主键

主键是表中的一列，被用作每一行的唯一标识符。它的功能有点像行的地址，并被用于表的主索引。(技术上讲，主键可以由多列组成，但为了我们的目的，让我们把它看作是一个单列）。

你可以把主键看作是一个行的ID号码。就像你的政府身份证能唯一地识别你，即使其他人与你同名或住在你的地址，一个表的主键列能唯一地识别每一行，即使该行的一些其他值与其他行共享。

因此，一个表的主键列必须是唯一的，它不能是空的或NULL 。

考虑一下我们样本数据库中的orders 表。在这个表中，主键是order_no ，这是一个唯一的数字，可以单独识别每个订单。

事实上，在这个表中，order_no 是唯一可以用作主键的列。user_id 可以包含重复的数据，因为同一个客户可以下一个以上的订单，而product_sku 也可以包含重复的数据，因为两个不同的客户可能订购同一个产品。

下面是我们的数据库的样子，每个表的主键列都突出显示。

what is a primary key

外键

外键将一个表中的数据与另一个表中的数据联系起来。一个表中的外键列指向另一个表中具有唯一值的列（通常是主键列），以创造一种交叉引用这两个表的方式。如果一个列被指定为外键，该列的每一行都必须包含它所引用的 "外键 "列中存在的一个值。被引用的（即 "外"）列必须只包含唯一的值--通常它是其表中的主键。

对于一个具体的例子，让我们再看一下我们数据库中的orders 表。这里的user_id 列与users 表的user_id 列相对应，而product_sku 列与books 表的product_sku 列相对应。

当我们设置这个表的时候，为orders.user_id 和orders.product_sku 添加外键规则是有意义的。

orders.user_id 应该引用users.user_id
orders.product_sku 应该引用books.product_sku

使用这些外键可以使我们不必重复存储相同的数据--我们不必在orders 表中存储用户的名字，因为我们可以使用orders.user_id 来引用该用户在users.user_id 中的唯一行，以获得他们的名字和其他相关信息。

但是，外键的真正目的是它们增加了一个限制：有外键的表的条目必须有一个与 "外 "表列相对应的值。

这种限制被称为外键约束。让我们更详细地了解一下外键约束。

什么是外键约束？

外键约束是当我们向表添加外键时创建的规则。表A 中的外键约束链接到表B 中具有唯一值的列，并且说A's 列中的一个值只有在它也存在于B's 列中时才有效。

(注意：外键可以是复合键，所以一个列的外键可以是另一个表中的两个或多个列。在这篇文章中，为了简单起见，我们将专注于把一个表中的单列链接到另一个表中的单列上）。

例如，想象一下，我们用前面布置的外键设置了我们的orders 表：orders.user_id 引用users.user_id ，orders.product_sku 引用books.product_sku 。这些规则意味着。

1.任何输入到`orders.user_id` 的值必须已经存在于`users.user_id` 中。

换句话说，订单必须由注册用户下达--如果orders.user_id 中的值在users.user_id 中不存在，orders 表将不接受新行或行更新。

2.2. 输入到`orders.product_sku` 中的任何数值必须已经存在于`books.product_sku` 中。

换句话说，用户只能订购存在于数据库中的产品 - 如果orders.product_sku 中的值不存在于books.product_sku 中，orders 表将不接受新行或行的更新。

外键如何工作

下面是一个关于外键约束如何工作的直观说明。

foreign key constraints

尽管这是一个简化的例子，我们可以看到外键约束如何帮助建立整个数据库中表之间的清晰关系，并通过使（例如）在orders 表中添加一条不存在于users 表中的用户而促进一致性。

请注意，外键不是强制性的，一个表可以没有外键。反之，一个表中的每一列都可能有一个外键约束。你在哪里使用外键，取决于你在数据库中存储的数据的具体情况，不同的数据点之间的关系，以及你希望你的数据在行被添加、更新或删除时如何被验证。

如何使用SQL的主键和外键

现在我们了解了什么是主键和外键，以及它们是如何工作的，让我们快速看一下，当我们在数据库中创建一个表时，我们如何分配这些值。

注意：我们将使用CockroachDB的SQL语法。不同类型的SQL可能会以稍微不同的方式处理这些任务，但我们将坚持使用Cockroach，因为它提供了一个免费的云数据库，对任何项目来说都是很好的选择

要从我们的数据库中创建带有外键的orders 表。

CREATE TABLE orders (
  order _no INT PRIMARY KEY,
  user_id INT REFERENCES users(user_id),
  product_sku INT REFERENCES books(product_sku),
);

在上面的代码中，我们将order_no 列设置为主键，然后以外键约束的形式为其他两列设置规则。

user_id 将作为外键（即中的任何值必须已经存在于中）。users.user_id orders.user_id users.user_id
product_sku 将作为外键（即中的任何值必须已经存在于中）。books.product_sku orders.product_sku books.product_sku

你可以检查一下CockroachDB数据库中已经存在哪些外键和其他约束。

SHOW CONSTRAINTS FROM orders;

如何用外键来处理更新、删除等问题

外键给我们提供了定义两个或多个表之间关系的能力。这很好，但这也意味着我们需要仔细考虑，当一个跨表链接的值被改变或删除时，会发生什么。

例如，假设我们书店的一个客户Mohamed要求我们删除他的账户和与之相关的所有数据。我们可以运行下面的查询，从users 表中删除他。

DELETE FROM users WHERE user_id = 11;

然而，按照我们目前设置表格的方式，这将只删除users 中的相关行。在orders 表中也有两个与该用户相关的订单，这些订单不会被自动删除；我们必须记住手动操作。

值得庆幸的是，有一个更简单的方法来处理这个问题!当我们添加外键时，我们也可以设置规则，如果一个跨表链接的值被改变，我们的数据库应该如何处理。

例如，在CockroachDB数据库中，我们可以创建一个这样的表。

CREATE TABLE orders (
  order _no INT PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  product_sku INT NOT NULL REFERENCES books(product_sku)ON DELETE CASCADE ON UPDATE CASCADE,
);

在上面的代码中，ON DELETE CASCADE 和ON UPDATE CASCADE 指定了当一个表中的行被删除或一个值被更新（分别）时，同样的操作应该在其他表中的链接值或行上执行。

因此，如果我们像这样建立我们的表，然后运行查询DELETE FROM users WHERE user_id = 11; ，与Mohamed（用户11 ）相关的数据将从用户表中删除，与他的账户相关的订单也将从orders 表中删除。

同样地，我们可以运行这个查询...

UPDATE users 
   SET user_id = 100 
 WHERE user_id = 11;

...Mohamed在users 中的user_id值将从11 更新到100 ，并且在orders 表中与他的订单相关的user_id 值也将被更新。

根据不同的情况，我们可能希望我们的数据库做一些不同的事情。例如，我们也可以使用ON DELETE SET NULL ，如果它所引用的表中的行被删除，就把引用行的所有列设置为NULL 。我们也可以指定我们希望数据库采取NO ACTION ，尽管这并不是严格意义上的必须，因为如果我们没有指定其他的操作，这就是数据库将遵循的默认规则。

在CockroachDB的文档中详细介绍了我们可以使用外键的全部操作。

测试一下自己：你掌握了外键吗？

用我们的假想数据库，以及我们在本文前面为orders 表添加的外键约束（orders.user_id 引用users.user_id ，orders.product_sku 引用books.product_sku ）来重温一下自己。

what is a foreign key - refresher

现在，看看你是否能回答以下问题。

运行以下SQL命令的结果是什么？

INSERT INTO orders (order_no, user_id, product_sku) 
VALUES (97, 14, 456);

答案：运行以下SQL命令的结果是什么？它将导致一个错误，并且该行不会被插入，因为user_id 的值14 不存在于users.user_id 中。

books 表是否有任何外键约束？

回答：没有。没有，在这种情况下，它也不需要任何约束。尽管orders 表引用了books.product_sku ，但是books 表中没有任何一列与其他表的值相对应，从而使添加外键约束变得有利。

运行以下SQL命令的结果是什么？

INSERT INTO orders (order_no, user_id, product_sku) 
VALUES (412, 10, 101);

答案：是的。这将导致一条带有这些值的新行被添加到orders 表中，因为它符合主键和外键对orders's 列的所有约束。

412 是一个唯一的值，不存在于，因此是有效的（主键约束）。order_no
10 是一个用户ID，对应于，因此是有效的（外键约束）。users.user_id
101 是一个产品SKU，对应于中现有的书，因此是有效的（一个外键约束）。books.product_sku

实践一下吧!

想建立一点关于外键的实际经验，并尝试在云中自己使用这个数据库吗？别担心，这只需要几分钟的时间!

第一步：注册一个免费的Cockroach Cloud账户，并按照这里的说明创建一个集群。

第2步：按照这里的说明，在本地安装CockroachDB。如果你已经安装了它，你可以跳过这一步。

第3步：下载这个.sql文件，其中包含创建上图中的数据库所需的SQL查询。在你的终端运行以下命令，数据和表将被自动添加到你的集群的defaultdb 数据库。

(注意，你需要用你的蟑螂云账户的具体细节来替换下面命令中的一些细节，并确保你已经为你的foreign_keys.sql 和cc-ca.crt 指定了正确的目录。)

cat foreign_keys.sql | cockroach sql --url 'postgres://username:password@free-tier.gcp-us-central1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=cc-ca.crt&options=--cluster=cluster-name'

第四步：在终端运行以下命令，启动CockroachDB SQL shell，并连接到CockroachCloud集群。

cockroach sql --url 'postgres://username:password@free-tier.gcp-us-central1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=cc-ca.crt&options=--cluster=cluster-name'

你已经进入了!你可以自由地浏览一下。CockroachDB关于外键的文档将是一个有用的参考。一些问题可以让你开始。

如果你试图放弃users 表会发生什么？
如果一个用户的账户被删除，orders 中会发生什么？(提示：SHOW CONSTRAINTS FROM orders;)
你能改变订单表中的外键约束，在删除一个链接值时执行不同的操作吗？
你能在这些表中添加新的列，并将它们与外键联系起来吗？

什么是外键？(附SQL实例)