如何使用SQL系列 之 如何在SQL中使用主键

引言

关系数据库的一个有价值的特性是将数据塑造成定义良好的结构。这种结构是通过使用具有固定列的表来实现的，遵循严格定义的数据类型，并确保每一行具有相同的形状。当你在表中以行形式存储数据时，能够准确地查找和引用它们也同样重要。在结构化查询语言(SQL)中，这可以通过主键来实现，主键在关系数据库中作为表中每一行的标识符。

在本教程中,您将了解主键和使用几个不同种类识别独特的数据库表中的行。使用一些示例数据集，你将在单列和多列上创建主键，以及自增顺序键。

前期准备

遵循这个指南,你将需要一个计算机运行一个基于sql的关系数据库管理系统(RDBMS)。本指南中的说明和示例使用以下环境进行了验证：

• 基本熟悉执行SELECT查询以从数据库中检索数据，如如何从SQL指南中的表中选择行所述。

注意：许多RDBMS使用它们自己的SQL实现。虽然触发提到作为一个SQL标准的一部分,标准不严格执行他们的语法或实现它们的方法。因此，它们的实现在不同的数据库中是不同的。本教程中概述的命令使用MySQL数据库的语法，可能无法在其他数据库引擎上工作。

你还需要一个数据库，其中一些表加载了示例数据，这样你就可以练习使用函数。我们鼓励您通过以下连接到示例数据库MySQL和建立一个连接到MySQL服务器上部分细节，创建测试数据库在本指南中使用的例子。

连接到MySQL并设置一个示例数据库

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器：

ssh sammy@your_server_ip
1

然后打开MySQL服务器提示符，将sammy替换为你的MySQL用户账户的名称：

mysql -u sammy -p
1

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

CREATE DATABASE primary_keys;
1

如果数据库成功创建,您将收到这样的输出：

OutputQuery OK, 1 row affected (0.01 sec)
1

要选择primary_keys数据库，运行以下USE语句：

USE primary_keys;
1

OutputDatabase changed
1

选择数据库后，您可以在其中创建示例表。现在你可以按照本指南的其余部分开始使用MySQL的主键了。

主键简介

关系型数据库中的数据以特定的、统一的结构存储在表中。表定义描述了哪些列以及哪些数据类型可以保存在各个列中。仅凭这一点就足以将信息存储在数据库中，并在以后使用不同的过滤条件(使用WHERE子句)找到它。然而，这种结构并不能保证无歧义地找到任何一行。

假设有一个数据库，其中包含所有允许在公共道路上行驶的注册车辆。数据库将包含汽车品牌、型号、制造年份和油漆颜色等信息。然而，如果你想找一辆2007年生产的红色雪佛兰科迈罗(Chevrolet Camaro)，你可以找到不止一辆。毕竟,汽车制造商出售类似的汽车到多个客户。这就是为什么注册的汽车都有唯一标识每辆车的车牌号码。如果你查找一辆牌照为“OFP857”的汽车，你可以确定这个标准只会找到一辆汽车。这是因为，根据法律，车牌号码是唯一标识注册车辆的。在关系型数据库中，这样的数据被称为主键。

主键是在一列或一组列中找到的唯一标识符，可以明确地标识数据库表中的每一行。一些规则反映了技术主键的属性：

• 主键必须使用唯一的值。如果主键包含多个列，那么这些列的组合在整个表中必须是唯一的。因为键的作用是唯一地标识每一行，所以它不能重复出现。
• 主键不能包含NULL值。
• 每个数据库表只能使用一个主键。

这些规则是由数据库引擎执行的，所以当主键定义在表上时，这些属性的值为true。

除了这些技术属性之外，您还必须考虑数据的内容，以决定什么样的数据适合作为主键。自然键是数据集中已经存在的标识符，而代理键是人工标识符。

有些数据结构的主键自然出现在数据集中，例如汽车数据库中的车牌号码或美国公民目录中的社会保险号。有时这种标识符不是单个值但一对或数的组合值。例如，在当地的城市房屋目录中，仅使用街道名称或街道编号不能唯一地标识房屋。一条街道上可以有多所房子，同样的数字可以出现在多条街道上。但是一对街道名称和数字可以被认为是唯一的房屋标识符。这种自然出现的标识符被称为自然密钥。

然而，数据通常不能由单个列或一小部分列的值来唯一地表征。然后，使用数字序列或随机生成的标识符(如UUIDs)创建人工主键。这样的键被称为代理键。

在接下来的几节中，我们将基于单列或多列创建自然键，并在无法使用自然键的表上生成代理键。

在单个列上创建主键

在很多情况下，一个数据集自然只包含一列，可以用来唯一地标识表中的行。在这种情况下，你可以创建一个自然键来描述数据。前面的示例数据库注册后车,想象一个表使用以下结构：

Sample table+---------------+-----------+------------+-------+------+
| 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 |
+---------------+-----------+------------+-------+------+
1
2
3
4
5
6
7
8

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

接下来，你将创建一个类似上面的表，license_plate列用作主键，并包含以下列：

• license_plate：该列保存车牌号码，由varchar数据类型表示。
• brand：这一列保存了汽车的品牌，使用varchar数据类型表示，不超过50个字符。
• model：这一列保存汽车的模型，使用varchar数据类型表示，最多不超过50个字符。
• color：该列保存颜色，使用varchar数据类型表示，最多不超过20个字符。
• year：这一列保存了汽车制造的年份，使用int数据类型来存储数值数据。

要创建cars表，执行以下SQL语句：

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

PRIMARY KEY子句紧跟在license_plate数据类型定义之后。当处理基于单个列的主键时，你可以使用简化的语法来创建键，在列定义中写primary key。

如果输出结果如下所示，说明表已经创建：

OutputQuery OK, 0 rows affected (0.00 sec)
1

接下来，通过运行以下INSERT INTO操作来加载上面示例中提供的示例行表：

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

数据库将返回成功消息：

OutputQuery OK, 4 rows affected (0.010 sec)
Records: 4  Duplicates: 0  Warnings: 0
1
2

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

SELECT * FROM cars;
1

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

Output+---------------+-----------+------------+-------+------+
| 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 |
+---------------+-----------+------------+-------+------+
1
2
3
4
5
6
7
8

接下来，您可以验证主键的规则是否由数据库引擎保证。尝试插入一辆车牌号重复的汽车：

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

MySQL将返回一个错误消息，说DEF456车牌将导致主键出现重复条目：

OutputERROR 1062 (23000): Duplicate entry 'DEF456' for key 'cars.PRIMARY'
1

注意：在底层，主键是用唯一的索引实现的，并且与你可能为表上的其他列手动创建的索引共享许多属性。最重要的是,主键索引也提高查询的性能表列上定义的索引。要了解有关为此目的使用索引的更多信息，请参阅本系列教程中的如何使用索引指南。

你现在可以确定重复的车牌是不允许的。接下来，检查是否可以插入一辆没有车牌的汽车：

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

这一次，数据库将返回另一条错误消息：

OutputERROR 1048 (23000): Column 'license_plate' cannot be null
1

通过数据库执行这两条规则，您可以确保license_plate唯一地标识表中的每一行。如果你对这张表进行任何牌照查询，每次都会返回一条记录。

在下一节中,您将了解如何使用多个主键列。

在多个列上创建主键

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

例如,想象一个注册中心的房屋,街道名称和街上就足以识别任何个人的房子数量：

Sample table+-------------------+---------------+-------------------+------+
| 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 |
+-------------------+---------------+-------------------+------+
1
2
3
4
5
6
7
8
9
10

街道名称Central Park West表中出现不止一次,所以街上数字“100”。但是，没有看到重复的街道名称和街道编号。在这种情况下，虽然没有一列是主键，但这两个值可以用来唯一地标识表中的每一行。

接下来，创建一个类似于上面的表，包含以下列：

• street_name：这一列保存了房子所在街道的名称，由varchar数据类型表示，其长度限制为50个字符。
• street_number：这一列保存了房子的街道编号，用varchar数据类型表示。这一列最多可以存储5个字符。它不使用数字int数据类型，因为一些街道编号可能包含字母(例如200B)。
• house_owner：这一列保存了房子主人的名字，由varchar数据类型表示，长度限制为50个字符。
• year：这一列保存了房子建造的年份，用存储数值的int数据类型表示。
  这一次，主键将使用street_name和street_number这两列，而不是单个列。为此，执行以下SQL语句：

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

与之前的例子不同，这一次，PRIMARY KEY子句出现在列定义的下方。PRIMARY KEY语句后面跟着一对括号，括号里有两个列名:street_name和street_number。这种语法在houses表中创建了两列的主键。

如果输出结果如下所示，说明表已经创建：

OutputQuery OK, 0 rows affected (0.00 sec)
1

接下来，通过运行以下INSERT INTO操作来加载包含前一个示例中示例行的表：

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

数据库将返回成功消息：

OutputQuery OK, 6 rows affected (0.000 sec)
Records: 6  Duplicates: 0  Warnings: 0
1
2

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

SELECT * FROM houses;
1

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

Output+-------------------+---------------+-------------------+------+
| 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 |
+-------------------+---------------+-------------------+------+
6 rows in set (0.000 sec)
1
2
3
4
5
6
7
8
9
10
11

现在，让我们验证数据库是否允许插入重复的街道名称和街道编号，但限制重复的完整地址出现在表中。让我们从在公园大道上添加另一栋房子开始：

INSERT INTO houses VALUES ('Park Avenue', '8000', 'Emily Brown', 2011);
1

MySQL将返回一条成功消息，因为地址’ 8000 Park Avenue '在前面的表中没有出现：

OutputQuery OK, 1 row affected (0.010 sec)
1

当你在8000 Main Street上添加一栋房子时，会得到类似的结果，重复使用街道编号：

INSERT INTO houses VALUES ('Main Street', '8000', 'David Jones', 2009);
1

同样，这将正确地插入新行，因为整个地址没有重复：

OutputQuery OK, 1 row affected (0.010 sec)
1

但是，尝试使用下面的INSERT语句在100 5th Avenue添加另一所房子：

INSERT INTO houses VALUES ('5th Avenue', '100', 'Josh Gordon', 2008);
1

数据库将返回一个错误消息，通知你5th Avenue和100这对值的主键有一个重复的条目：

OutputERROR 1062 (23000): Duplicate entry '5th Avenue-100' for key 'houses.PRIMARY'
1

数据库正确地执行了主键规则，将键定义在一对列上。您可以确保由街道名称和街道编号组成的完整地址不会在表中出现重复。

在本节中，我们创建了一个由两列组成的自然键来唯一标识house表中的每一行。但这并不总是可能得到数据集的主键。在下一节中，我们将使用不直接从数据中获取的人工主键。

创建顺序主键

到目前为止，我们已经使用样本数据集中已有的列创建了唯一的主键。但在某些情况下，数据不可避免地会重复，使得任何列都无法成为良好的唯一标识符。在这些情况下，您可以使用生成的标识符创建顺序主键。当你需要设计新的标识符来唯一标识行时，在这些人工标识符上创建的主键称为代理键。

想象的读书俱乐部成员一次非正式聚会,任何人都可以加入不显示政府ID。名字匹配的人有可能在某个时间加入俱乐部：

Sample table+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
| Jane       | Smith     |
| Bob        | Johnson   |
| Samantha   | Davis     |
| Michael    | Rodriguez |
| Tom        | Thompson  |
| Sara       | Johnson   |
| David      | Jones     |
| Jane       | Smith     |
| Bob        | Johnson   |
+------------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Bob Johnson和Jane Smith这两个名字在表格中重复出现。你需要使用一个额外的标识符来确定谁是谁，而且无论如何都不可能唯一地标识该表中的行。如果你在纸上不停地读书俱乐部成员的列表,你可以保持辅助标识符来帮助区分当中唯一的相同的名称。

在关系数据库中，你也可以通过使用一个额外的列来保存生成的、无事实依据的标识符，唯一地分隔表中的所有行。我们把它命名为member_id。

但是，当您想向数据库添加另一个图书俱乐部成员时，使用这样的标识符将是一种负担。为了解决这个问题，MySQL为自动递增的数字列提供了一个功能，其中数据库自动提供一个递增的整数序列的列值。

让我们创建一个类似于上面所示的表。你将添加一个额外的自动递增列(member_id)来保存为每个俱乐部成员自动分配的编号。自动分配数量将作为表的主键：

• member_id：这一列保存着一个自动递增的数字标识符，以int数据类型表示。
• first_name：这一列保存了俱乐部成员的名字，由varchar数据类型表示，限制为50个字符。
• last_name：这一列保存了俱乐部成员的姓氏，由varchar数据类型表示，限制为50个字符。

要创建表，执行以下SQL语句：

CREATE TABLE club_members (
    member_id int AUTO_INCREMENT PRIMARY KEY,
    first_name varchar(50),
    last_name varchar(50)
);
1
2
3
4
5

虽然PRIMARY KEY子句出现在列类型定义之后，就像单个列主键一样，但在它之前出现了一个额外的属性:AUTO_INCREMENT。如果没有明确指定，它会告诉MySQL自动为该列生成一个递增的数字序列。

注意：用于定义列的AUTO_INCREMENT属性是MySQL特有的。其他数据库通常也提供类似的方法来生成顺序键，但不同引擎的语法有所不同。如果有疑问，我们建议你参考RDBMS的官方文档。

如果输出结果如下所示，说明表已经创建：

OutputQuery OK, 0 rows affected (0.00 sec)
1

接下来，通过运行以下INSERT INTO操作来加载上面示例中提供的示例行表：

INSERT INTO club_members (first_name, last_name) VALUES
    ('John', 'Doe'),
    ('Jane', 'Smith'),
    ('Bob', 'Johnson'),
    ('Samantha', 'Davis'),
    ('Michael', 'Rodriguez'),
    ('Tom', 'Thompson'),
    ('Sara', 'Johnson'),
    ('David', 'Jones'),
    ('Jane', 'Smith'),
    ('Bob', 'Johnson');
1
2
3
4
5
6
7
8
9
10
11

INSERT语句现在包含了列名的列表(first_name和last_name)，这可以确保数据库知道数据集中没有提供member_id列，因此应该使用它的默认值。

数据库将返回成功消息：

OutputQuery OK, 10 rows affected (0.002 sec)
Records: 10  Duplicates: 0  Warnings: 0
1
2

使用SELECT语句来验证新创建表中的数据：

SELECT * FROM club_members;
1

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

Output+-----------+------------+-----------+
| member_id | first_name | last_name |
+-----------+------------+-----------+
|         1 | John       | Doe       |
|         2 | Jane       | Smith     |
|         3 | Bob        | Johnson   |
|         4 | Samantha   | Davis     |
|         5 | Michael    | Rodriguez |
|         6 | Tom        | Thompson  |
|         7 | Sara       | Johnson   |
|         8 | David      | Jones     |
|         9 | Jane       | Smith     |
|        10 | Bob        | Johnson   |
+-----------+------------+-----------+
10 rows in set (0.000 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

然而，这一次，member_id列出现在结果中，包含从1到10的数字序列。有了这一列，Jane Smith和Bob Johnson的重复行就不再无法区分了，因为每个名字都有一个唯一的标识符(member_id)。

现在，来验证数据库是否允许在俱乐部成员列表中再添加一个Tom Thompson：

INSERT INTO club_members (first_name, last_name) VALUES ('Tom', 'Thompson');
1

MySQL将返回一条成功消息：

OutputQuery OK, 1 row affected (0.009 sec)
1

要检查数据库分配给新条目的数字标识符，再次执行SELECT查询：

SELECT * FROM club_members;
1

输出中还有一行：

Output+-----------+------------+-----------+
| member_id | first_name | last_name |
+-----------+------------+-----------+
|         1 | John       | Doe       |
|         2 | Jane       | Smith     |
|         3 | Bob        | Johnson   |
|         4 | Samantha   | Davis     |
|         5 | Michael    | Rodriguez |
|         6 | Tom        | Thompson  |
|         7 | Sara       | Johnson   |
|         8 | David      | Jones     |
|         9 | Jane       | Smith     |
|        10 | Bob        | Johnson   |
|        11 | Tom        | Thompson  |
+-----------+------------+-----------+
11 rows in set (0.000 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

通过数据库的AUTO_INCREMENT特性，在member_id列中自动为新行分配了编号11。

如果要处理的数据没有天然的候选主键，而且你也不想每次向数据库添加新数据时都凭空捏造标识符，那么可以放心地将按顺序生成的标识符作为主键。

总结

通过本指南，您了解了什么是主键，以及如何在MySQL中创建常用类型来标识数据库表中唯一的行。你构建了自然主键，创建了跨多列的主键，并在没有自然键的情况下使用了自动递增的顺序键。

你可以使用主键进一步塑造数据库结构，确保数据行是唯一可识别的。