SQL主键详解：创建、使用与管理终极指南

引言

关系型数据库的一个重要特性是能够将数据塑造成一个明确定义的结构。这种结构通过使用具有固定列、严格定义的数据类型以及确保每一行具有相同形状的表来实现。当数据以行的形式存储在表中时，能够无歧义地找到并引用它们同样重要。在结构化查询语言（SQL）中，这可以通过主键来实现，主键作为关系数据库中表中各个行的唯一标识符。

在本教程中，您将学习关于主键的知识，并使用几种不同类型的主键来识别数据库表中的唯一行。通过使用一些示例数据集，您将在单个列和多个列上创建主键，并自动递增顺序键。

先决条件

为了按照本指南操作，你需要一台安装了基于SQL的关系型数据库管理系统（RDBMS）的计算机。本指南中的指示和示例是在以下环境下验证的：

• 运行 Ubuntu 20.04 的服务器，拥有具有管理权限的非根用户，并配置了 UFW 防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南 中所述。
• 在服务器上安装并安全配置了 MySQL，如 如何在 Ubuntu 20.04 上安装 MySQL 中所述。本指南已通过非根 MySQL 用户验证，该用户是按照第 3 步中描述的过程创建的。
• 基本熟悉执行 SELECT 查询以从数据库中检索数据，如我们的 如何在 SQL 中从表中选择行 指南中所述。

连接到 MySQL 并设置一个样本数据库

在本节中，您将连接到一个 MySQL 服务器并创建一个示例数据库，以便您可以按照本指南中的示例进行操作。

如果你的 SQL 数据库系统在远程服务器上运行，从本地机器上用 SSH 登录到你的服务器。

ssh sammy@your_server_ip

然后打开 MySQL 服务器提示符，将 sammy 替换为您的 MySQL 用户帐户的名称。

mysql -u sammy -p

创建一个名为 primary_keys 的数据库。

CREATE DATABASE primary_keys;

如果数据库成功创建，您将收到如下输出：

Query OK, 1 row affected (0.01 sec)

要选择 primary_keys 数据库，请执行以下 USE 语句:

USE primary_keys;

您将收到以下输出：

Database changed

选择数据库后，您可以在其中创建示例表格。现在，您已准备好按照指南的剩余部分开始使用 MySQL 中的主键。

主键介绍

关系数据库中的数据以特定的、具有统一结构的行形式存储在表中。表定义描述了有哪些列以及可保存在各个列中的数据类型。仅凭这些信息，就足以将数据存储在数据库中，并能够在以后通过使用 WHERE 子句和不同的筛选条件来找到它。然而，这种结构不能保证能够找到任何单独的行并排除二义性。

想象一下，有一个数据库记录了所有被允许在公共道路上行驶的注册车辆。该数据库将包含汽车品牌、型号、制造年份和车漆颜色等信息。但是，如果你正在寻找一辆 2007 年制造的红色雪佛兰 Camaro，可能会找到多辆符合条件的车辆。毕竟，汽车制造商会向多个客户销售类似的车型。这就是为什么注册车辆都有独一无二的车牌号码来标识每辆车。如果你查询的车辆是 OFP857，你可以确定这个条件只会找到一辆车。这是因为，按照法律规定，车牌号码可以唯一地标识注册车辆。在关系型数据库中，这样的数据被称为主键。

主键是在数据库表中找到的单个列或一组列中的唯一标识符，能够明确地识别每一行。几个规则反映了主键的技术属性。

• 主键必须使用唯一值。如果主键由多个列组成，这些列中值的组合在整个表中必须是唯一的。由于键旨在唯一标识每一行，因此它不能出现多次。
• 主键不能包含 NULL 值。
• 每个数据库表只能使用一个主键。

如果在表上定义了主键，数据库引擎将强制执行这些规则，因此您可以依赖这些属性是真实的。

除了这些技术属性之外，您还必须考虑数据本身的内容，以决定哪种数据适合成为主键。自然键是已经存在于数据集中的标识符，而代理键是人为生成的标识符。

有些数据结构具有在数据集中自然发生的主键，例如在汽车数据库中的车牌号码或美国公民目录中的社会安全号码。有时这些标识符不是单个值，而是一对或几个值的组合。例如，在本地的房屋目录中，仅使用街道名称或街道号码无法唯一标识一栋房屋。同一条街上可能有多栋房屋，同一个号码可能出现在多条街上。但是一对街道名称和号码可以被认为是一个唯一的房屋标识符。这种自然发生的标识符被称为自然键。

然而，通常数据不能仅通过单个列或小的列子集的值来唯一描述。这时，会创建人工的主键，例如使用数字序列或随机生成的标识符（如 UUID）。这样的键被称为替代键。

在接下来的部分中，您将根据单个列或多个列创建自然键，并在自然键不可用的表上生成代理键。

在单个列上创建主键

在许多情况下，数据集自然地包含一个可用于唯一标识表中行的单列。在这些情况下，您可以创建一个自然键来描述数据。继上一个已注册汽车数据库的示例，想象一个具有以下结构的表：

这是文章《如何在SQL中使用主键》的第2部分（共8部分）。

+---------------+-----------+------------+-------+------+
| license_plate | brand     | model      | color | year |
+---------------+-----------+------------+-------+------+
| ABC123        | Ford      | Mustang    | Red   | 2018 |
| CES214        | Ford      | Mustang    | Red   | 2018 |
| DEF456        | Chevrolet | Camaro     | Blue  | 2016 |
| GHI789        | Dodge     | Challenger | Black | 2014 |
+---------------+-----------+------------+-------+------+

第一行和第二行都描述了一辆2018年的红色福特野马。仅凭汽车制造商和型号，您无法唯一地识别该车。而车牌在这两种情况下是不同的，为表中每一行提供了一个良好的唯一标识符。因为车牌号码已经是数据的一部分，将其作为主键使用会创建一个自然键。如果您在license_plate列上创建表时没有使用主键，那么在某个时间点上数据集中可能会出现重复或空白的车牌。

接下来，您将创建一个与上面相似的表格，其中使用license_plate列作为主键，并添加以下列：

• license_plate: 此列存储车牌号码，数据类型为varchar。
• brand: 此列存储汽车品牌，数据类型为varchar，最大长度为50个字符。
• model: 此列存储汽车型号，数据类型为varchar，最大长度为50个字符。
• color: 此列存储汽车颜色，数据类型为varchar，最大长度为20个字符。
• year: 此列存储汽车制造年份，数据类型为int，用于存储数值数据。

要创建汽车表，请执行以下SQL语句。

1. CREATE TABLE cars (
2. license_plate varchar(8) PRIMARY KEY,
3. brand varchar(50),
4. model varchar(50),
5. color varchar(20),
6. year int
7. );

主键约束紧跟在“license_plate”数据类型定义之后。当处理基于单列的主键时，您可以使用简化的语法来创建主键，即在列定义中写入“PRIMARY KEY”。

如果打印出以下输出，则表示表已成功创建：

Query OK, 0 rows affected (0.00 sec)

接着，通过运行以下的INSERT INTO操作，将上方例子中呈现的样本行加载到表格中。

1. INSERT INTO cars VALUES
2. (‘ABC123’, ‘Ford’, ‘Mustang’, ‘Red’, 2018),
3. (‘CES214’, ‘Ford’, ‘Mustang’, ‘Red’, 2018),
4. (‘DEF456’, ‘Chevrolet’, ‘Camaro’, ‘Blue’, 2016),
5. (‘GHI789’, ‘Dodge’, ‘Challenger’, ‘Black’, 2014);

数据库将返回成功消息。

这是文章《如何在SQL中使用主键》的第3部分（共8部分）。

Query OK, 4 rows affected (0.010 sec) Records: 4 Duplicates: 0 Warnings: 0

现在，您可以使用SELECT语句来验证新创建的表是否包含预期的数据和格式。

1. SELECT * FROM cars;

输出将显示一个类似于本节开头的表格。

接下来，您可以验证数据库引擎是否保证了主键规则。通过执行以下操作尝试插入一辆具有重复车牌号的汽车：

1. INSERT INTO cars VALUES ('DEF456', 'Jeep', 'Wrangler', 'Yellow', 2019);

MySQL将会返回一个错误信息，指出“DEF456”车牌会导致主键重复。

在下一部分中，您将学习如何使用多列主键。

在多个列上创建主键

当一列不足以唯一标识表中的一行时，可以创建使用多列的主键。

例如，想象一下一个房屋登记册，其中无论街道名称还是门牌号码都无法单独识别任何一个房屋。

这是文章《如何在SQL中使用主键》的第4部分（共8部分）。

+-------------------+---------------+-------------------+------+
| street_name       | street_number | house_owner       | year |
+-------------------+---------------+-------------------+------+
| 5th Avenue        | 100           | Bob Johnson       | 2018 |
| Broadway          | 1500          | Jane Smith        | 2016 |
| Central Park West | 100           | John Doe          | 2014 |
| Central Park West | 200           | Tom Thompson      | 2015 |
| Lexington Avenue  | 5001          | Samantha Davis    | 2010 |
| Park Avenue       | 7000          | Michael Rodriguez | 2012 |
+-------------------+---------------+-------------------+------+

在表格中，“中央公园西路”这个街道名称出现多次，“100”这个街道号码也是如此。然而，没有重复的街道名称和街道号码的组合。在这种情况下，虽然没有单独的列可以作为主键，但这两个值的组合可以唯一地标识表格中的每一行。

接下来，您将创建一个类似上面显示的表格，包含以下列：

• street_name：此列存储房屋所在的街道名称，数据类型为varchar，限制为50个字符。
• street_number：此列存储房屋的街道号码，数据类型为varchar。此列最多可存储5个字符。它不使用数值型int数据类型，因为某些街道号码可能包含字母（例如，200B）。
• house_owner：此列存储房屋所有者的姓名，数据类型为varchar，限制为50个字符。
• year：此列存储房屋建造的年份，数据类型为int，用于存储数值。

这次，主键将使用街道名称和街道号码两列，而不是单独的一列。为此，请执行以下SQL语句：

1. CREATE TABLE houses (
2. street_name varchar(50),
3. street_number varchar(5),
4. house_owner varchar(50),
5. year int,
6. PRIMARY KEY(street_name, street_number)
7. );

这一次，主键子句出现在列定义之下，与上一个例子不同。主键语句之后带有括号，里面包含两个列名：street_name和street_number。这种语法在houses表中创建了跨越两列的主键。

如果以下输出打印，则表示表已成功创建：

这是文章《如何在SQL中使用主键》的第5部分（共8部分）。

Query OK, 0 rows affected (0.00 sec)

接着，通过执行以下的INSERT INTO操作，用前一个示例中呈现的样本行加载表格。

1. INSERT INTO houses VALUES
2. (‘Central Park West’, ‘100’, ‘John Doe’, 2014),
3. (‘Broadway’, ‘1500’, ‘Jane Smith’, 2016),
4. (‘5th Avenue’, ‘100’, ‘Bob Johnson’, 2018),
5. (‘Lexington Avenue’, ‘5001’, ‘Samantha Davis’, 2010),
6. (‘Park Avenue’, ‘7000’, ‘Michael Rodriguez’, 2012),
7. (‘Central Park West’, ‘200’, ‘Tom Thompson’, 2015);

数据库将以成功消息做出回应。

您现在可以使用SELECT语句来验证新创建的表是否包含预期的数据和格式。

1. SELECT * FROM houses;

输出将显示一个类似于本节开头的表格。

现在让我们验证一下数据库是否允许插入具有相同街道名称和街道号码的行，但是限制在表中出现重复的完整地址。让我们首先在公园大道上再添加一所房子。

1. INSERT INTO houses VALUES (‘Park Avenue’, ‘8000’, ‘Emily Brown’, 2011);

由于之前的表中没有出现8000 Park Avenue的地址，MySQL将会返回一个成功的消息。

这是文章《如何在SQL中使用主键》的第6部分（共8部分）。

Query OK, 1 row affected (0.010 sec)

当您在主街8000号增加一栋房屋时，重复的街道编号将导致类似的结果。

1. INSERT INTO houses VALUES (‘Main Street’, ‘8000’, ‘David Jones’, 2009);

由于完整的地址不重复，再次插入将正确地插入新行。