如何在SQL中使用主键
引言
关系型数据库的一个有价值的特性之一是将数据塑造成一个明确定义的结构。通过使用具有固定列、严格定义的数据类型的表，并确保每一行具有相同的形状来实现这种结构。当将数据以行的形式存储在表中时，能够无歧义地找到它们并引用它们同样重要。在结构化查询语言（SQL）中，可以通过主键来实现这一点，主键作为关系数据库中表中各个行的标识符。
在本教程中，您将学习关于主键的知识，并使用几种不同类型的主键来识别数据库表中的唯一行。通过使用一些示例数据集，您将在单个列和多个列上创建主键，并自动递增顺序键。
先决条件
为了按照本指南操作，你需要一台安装了基于SQL的关系型数据库管理系统（RDBMS）的计算机。本指南中的指示和示例是在以下环境下验证的：
- A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
- MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.
- Basic familiarity with executing SELECT queries to retrieve data from the database as described in our How To SELECT Rows FROM Tables in SQL guide.
Note
您还需要一个空的数据库，您可以在其中创建表格来展示主键的使用。我们鼓励您阅读《连接到MySQL和设置示例数据库》部分，了解如何连接到MySQL服务器并创建此指南中示例中使用的测试数据库的详细信息。
连接到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，你可以确定这个条件只会找到一辆车。这是因为，按照法律规定，车牌号码可以唯一地标识注册车辆。在关系型数据库中，这样的数据被称为主键。
主键是在数据库表中找到的单个列或一组列中的唯一标识符，能够明确地识别每一行。几个规则反映了主键的技术属性。
- A primary key must use unique values. If the primary key consists of more than one column, the combination of values in these columns must be unique across the whole table. Since the key is meant to identify every row uniquely, it can’t appear more than once.
- A primary key must not contain NULL values.
- Each database table can use only one primary key.
如果在表上定义了主键，数据库引擎将强制执行这些规则，因此您可以依赖这些属性是真实的。
除了这些技术属性之外，您还必须考虑数据本身的内容，以决定哪种数据适合成为主键。自然键是已经存在于数据集中的标识符，而代理键是人为生成的标识符。
有些数据结构具有在数据集中自然发生的主键，例如在汽车数据库中的车牌号码或美国公民目录中的社会安全号码。有时这些标识符不是单个值，而是一对或几个值的组合。例如，在本地的房屋目录中，仅使用街道名称或街道号码无法唯一标识一栋房屋。同一条街上可能有多栋房屋，同一个号码可能出现在多条街上。但是一对街道名称和号码可以被认为是一个唯一的房屋标识符。这种自然发生的标识符被称为自然键。(Translated by OpenAI)
然而，通常数据不能仅通过单个列或小的列子集的值来唯一描述。这时，会创建人工的主键，例如使用数字序列或随机生成的标识符（如UUID）。这样的键被称为替代键。
在接下来的部分中，您将根据单个列或多个列创建自然键，并在自然键不可用的表上生成代理键。
在单个列上创建主键。
在许多情况下，数据集自然地包含一个可用于唯一标识表中行的单列。在这些情况下，您可以创建一个自然键来描述数据。继上一个已注册汽车数据库的示例，想象一个具有以下结构的表：
+—————+———–+————+——-+——+ | 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: This column holds the license plate number, represented by the varchar data type.
- brand: This column holds the brand of the car, expressed using the varchar data type with a maximum of 50 characters.
- model: This column holds the car’s model, expressed using the varchar data type with a maximum of 50 characters.
- color: This column holds the color, expressed using the varchar data type with a maximum of 20 characters.
- year: This column holds the year the car was made, expressed using the int data type to store numerical data.
要创建汽车表，请执行以下SQL语句。
- CREATE TABLE cars (
- license_plate varchar(8) PRIMARY KEY,
- brand varchar(50),
- model varchar(50),
- color varchar(20),
- year int
- );
主键约束跟在 “license_plate” 数据类型定义之后。当处理基于单列的主键时，您可以使用简化的语法来创建主键，在列定义中写入 “PRIMARY KEY”。
如果打印出以下输出，则表示表已创建：
Query OK, 0 rows affected (0.00 sec)
接着，通过运行以下的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);
数据库将返回成功消息。
Query OK, 4 rows affected (0.010 sec) Records: 4 Duplicates: 0 Warnings: 0
现在，您可以使用SELECT语句来验证新创建的表是否包含预期的数据和格式。
- SELECT * FROM cars;
输出将显示一个类似于本节开头的表格。
+—————+———–+————+——-+——+ | 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 | +—————+———–+————+——-+——+
接下来，您可以验证数据库引擎是否保证了主键规则。通过执行以下操作尝试插入一辆具有重复车牌号的汽车：
- INSERT INTO cars VALUES (‘DEF456‘, ‘Jeep’, ‘Wrangler’, ‘Yellow’, 2019);
MySQL将会返回一个错误信息，指出“DEF456”车牌会导致主键重复。
ERROR 1062 (23000): Duplicate entry ‘DEF456’ for key ‘cars.PRIMARY’
Note
你现在可以确定不允许有重复车牌。接下来，请检查是否可能插入一辆没有车牌的汽车。
- INSERT INTO cars VALUES (NULL, ‘Jeep’, ‘Wrangler’, ‘Yellow’, 2019);
这一次，数据库将会用另一条错误信息回应。
ERROR 1048 (23000): Column ‘license_plate’ cannot be null
通过数据库施行的这两条规则，你可以确信车牌能够唯一地标识表中的每一行。如果你针对任何车牌查询表格，每次你都可以期望只返回一行记录。
在下一部分中，您将学习如何使用多列主键。
在多个列上创建主键。
当一列不足以唯一标识表中的一行时，可以创建使用多列的主键。
例如，想象一下一个房屋登记册，其中无论街道名称还是门牌号码都无法单独识别任何一个房屋。
+——————-+—————+——————-+——+ | 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: This column holds the name of the street where the house is located, represented by the varchar data type limited to 50 characters.
- street_number: This column holds the house’s street number, represented by the varchar data type. This column can store up to 5 characters. It doesn’t use numerical int data type because some street numbers might contain letters (for example, 200B).
- house_owner: This column holds the name of the house’s owner, represented by the varchar data type limited to 50 characters.
- year: This column holds the year the house was built, represented by the int data type to store numerical values.
这次，主键将使用街道名称和街道号码两列，而不是单独的一列。为此，请执行以下SQL语句：
- CREATE TABLE houses (
- street_name varchar(50),
- street_number varchar(5),
- house_owner varchar(50),
- year int,
- PRIMARY KEY(street_name, street_number)
- );
这一次，主键子句出现在列定义之下，与上一个例子不同。主键语句之后带有括号，里面包含两个列名：street_name和street_number。这种语法在houses表中创建了跨越两列的主键。
如果以下输出打印，则表示表已经创建：
Query OK, 0 rows affected (0.00 sec)
接着，通过执行以下的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);
数据库将以成功消息做出回应。
Query OK, 6 rows affected (0.000 sec) Records: 6 Duplicates: 0 Warnings: 0
您现在可以使用SELECT语句来验证新创建的表是否包含预期的数据和格式。
- SELECT * FROM houses;
输出将显示一个类似于本节开头的表格。
+——————-+—————+——————-+——+ | 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)
现在让我们验证一下数据库是否允许插入具有相同街道名称和街道号码的行，但是限制在表中出现重复的完整地址。让我们首先在公园大道上再添加一所房子。
- INSERT INTO houses VALUES (‘Park Avenue’, ‘8000’, ‘Emily Brown’, 2011);
由于之前的表中没有出现8000 Park Avenue的地址，MySQL将会返回一个成功的消息。
Query OK, 1 row affected (0.010 sec)
当您在8000 Main Street上增加一栋房屋时，重复街道编号将导致类似的结果。
- INSERT INTO houses VALUES (‘Main Street’, ‘8000’, ‘David Jones’, 2009);
由于完整的地址不重复，再次插入将正确地插入新行。
Query OK, 1 row affected (0.010 sec)
然而，尝试使用下面的INSERT语句在第五大道100号上增加另一栋房子。
- INSERT INTO houses VALUES (‘5th Avenue’, ‘100’, ‘Josh Gordon’, 2008);
数据库将返回一个错误消息，通知您有关主键对值为第5大道和100存在重复项的信息。
ERROR 1062 (23000): Duplicate entry ‘5th Avenue–100‘ for key ‘houses.PRIMARY’
该数据库按照主键规则进行了正确的实施，该键被定义在一对列上。您可以确信表中不会重复出现由街道名称和门牌号组成的完整地址。
在本部分中，您使用一对列创建了一个自然键，以唯一地识别房屋表中的每一行。但是，并不总是能够从数据集中派生主键。在下一部分中，您将使用不直接来自数据的人工主键。
创建一个连续的主键
直到现在，您已经在示例数据集中使用现有列创建了独特的主键。但在某些情况下，数据不可避免地会重复，使得任何列都无法成为良好的唯一标识符。在这些情况下，您可以使用生成的标识符创建连续的主键。当您手头的数据需要您设计新的标识符来唯一标识行时，基于这些人工标识符创建的主键被称为代理键。
想象一下一个书友会成员名单——这是一个非正式的聚会，任何人都可以在不出示政府签发的身份证明的情况下加入。有可能会有同名的人在某个时间加入这个俱乐部。
+————+———–+ | first_name | last_name | +————+———–+ | John | Doe | | Jane | Smith | | Bob | Johnson | | Samantha | Davis | | Michael | Rodriguez | | Tom | Thompson | | Sara | Johnson | | David | Jones | | Jane | Smith | | Bob | Johnson | +————+———–+
表格中重复出现了Bob Johnson和Jane Smith这两个名字。你需要使用一个额外的识别符来确保谁是谁，并且无法以任何方式唯一地识别表格中的行。如果你将书友俱乐部成员名单保留在纸上，你可以使用辅助识别符来帮助区分同名的人。
在关系数据库中，您可以通过使用一个额外的列来执行类似的操作，该列用于保存生成的无实际含义的标识符，其唯一目的是唯一地区分表中的所有行。我们可以将其称为成员ID。
然而，每当您想将另一个读书俱乐部成员添加到数据库中时，创建这样的标识符将是一种负担。为了解决这个问题，MySQL提供了自动递增数字列的功能，数据库会通过递增整数序列自动提供列的值。
让我们创建一个与上面所示相似的表格。您将添加一个额外的自增列（member_id），用于保存每个俱乐部成员的自动分配号码。这个自动分配的号码将作为该表的主键。
- member_id: This column holds an auto-incremented, numerical identifier represented by the int data type.
- first_name: This column holds the first name of the club members, represented by the varchar data type limited to 50 characters.
- last_name: This column holds the last name of the club members, represented by the varchar data type limited to 50 characters.
创建表格，执行以下SQL语句。
- CREATE TABLE club_members (
- member_id int AUTO_INCREMENT PRIMARY KEY,
- first_name varchar(50),
- last_name varchar(50)
- );
虽然 PRIMARY KEY 子句出现在列类型定义之后，就像单列主键一样，但在它之前还有一个额外的属性：AUTO_INCREMENT。它告诉MySQL在没有显式提供的情况下自动生成该列的值，使用递增的序列号。
Note
如果出现以下输出，则表示表已创建：
Query OK, 0 rows affected (0.00 sec)
随后，通过运行以下的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’);
现在的INSERT语句包括了列名的列表（first_name和last_name），这样数据库就知道数据集中没有提供member_id列，所以应该使用它的默认值。
数据库将会以成功信息作出回应。
Query OK, 10 rows affected (0.002 sec) Records: 10 Duplicates: 0 Warnings: 0
使用SELECT语句来验证新创建的表中的数据。
- SELECT * FROM club_members;
输出结果将会显示一个类似于本节开始部分的表格。
+———–+————+———–+ | 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)
然而，这一次，结果中出现了member_id列，其中包含从1到10的数字序列。有了这一列，Jane Smith和Bob Johnson的重复行不再无法区分，因为每个姓名都与一个唯一标识符（member_id）相关联。
现在，让我们来验证一下数据库是否允许将另一个汤姆·汤普森添加到俱乐部会员名单中。
- INSERT INTO club_members (first_name, last_name) VALUES (‘Tom‘, ‘Thompson‘);
MySQL会以成功消息进行回应。
Query OK, 1 row affected (0.009 sec)
要查看数据库为新条目分配的数字标识符，请再次执行SELECT查询。 de , SELECT
- SELECT * FROM club_members;
输出中多了一行。
+———–+————+———–+ | 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)
通过数据库的AUTO_INCREMENT特性，自动为member_id列分配了编号为11的新行。
如果你正在处理的数据没有自然的候选主键，并且你不想每次向数据库添加新数据时提供虚构的标识符，那么你可以安全地依赖于顺序生成的标识符作为主键。
结论
通过遵循本指南，你学会了什么是主键以及如何在MySQL中创建常见类型的主键，以便在数据库表中标识唯一行。你创建了自然主键，创建了跨多列的主键，并在没有自然主键的情况下使用了自增序列键。
您可以使用主键来进一步定义数据库结构，以确保数据行能够唯一标识。本教程只涵盖了使用主键的基础知识。要了解更多相关信息，请参考MySQL关于约束的文档。您还可以查阅我们的SQL约束理解指南和如何在SQL中使用约束的指南。
如果您想更多了解关于SQL语言的不同概念和与之相关的操作技巧，我们鼓励您查看《如何使用SQL系列》中的其他指南。
