作者选择了 多样性在技术基金作为 写给捐款计划的一部分接受捐款。
介绍
关系数据库的一个有价值的特征是将数据塑造成一个明确的结构。这种结构是通过使用具有固定的列的表来实现的,遵循严格定义的数据类型,并确保每个行都具有相同的形状。当您将数据存储为表中的行时,同样重要的是能够找到它们并无疑地参考它们。在 结构化查询语言(SQL)中,这可以用 primary keys来实现,它作为关系数据库中表中的单个行的标识符。
在本教程中,您将了解主键,并使用几种不同的类型来识别数据库表中的唯一行. 使用一些样本数据集,您将创建单列和多个列上的主键,并自动增加序列密钥。
前提条件
要遵循本指南,您需要运行基于 SQL 的关系数据库管理系统(RDBMS)的计算机。
- 运行 Ubuntu 20.04 的服务器,具有非 root 用户的管理权限和与 UFW 配置的防火墙,如我们在 Ubuntu 20.04 的初始服务器设置指南中所描述)。
- MySQL 安装并在服务器上安全,如在 [Ubuntu 20.04 上如何安装 MySQL]中所述(https://andsky.com/tech/tutorials/how-to-install-mysql-on-ubuntu-20-04)。
<$>[注] **注:**请注意,许多RDBMS使用自己的独特的SQL实现程序,虽然本教程中描述的命令将在大多数RDBMS上工作,而主键是SQL标准的一部分,但有些功能是数据库特定的,因此,如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。
您还需要一个空的数据库,在其中您将能够创建表,展示使用主密钥. 我们鼓励您通过以下 连接到MySQL和设置示例数据库部分,了解有关连接到MySQL服务器和创建测试数据库的详细信息,并在本指南中使用示例。
连接到MySQL并设置样本数据库
在本节中,您将连接到MySQL服务器并创建一个样本数据库,以便您可以按照本指南中的示例。
如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:
1[environment local]
2ssh sammy@your_server_ip
然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:
1mysql -u sammy -p
创建一个名为 primary_keys 的数据库:
1CREATE DATABASE primary_keys;
如果数据库创建成功,您将收到这样的输出:
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
若要選擇「primary_keys」資料庫,請執行下列「USE」聲明:
1USE primary_keys;
您将获得以下输出:
1[secondary_label Output]
2Database changed
选择数据库后,您可以在数据库中创建示例表,您现在已经准备好遵循其余的指南,并开始在MySQL中使用主键。
首要钥匙的介绍
关系数据库中的数据存储在具有特定、统一的单个行结构的表中。 表定义描述了哪些列存在,以及哪些数据类型可以存储在单个列中。 仅此就足以将信息存储在数据库中,并通过使用WHERE条款(WHERE条款)后使用不同的过滤标准来找到它。
想象一下所有被允许在公共道路上驾驶的注册车辆的数据库。该数据库将包含汽车品牌、车型、制造年份和油漆颜色等信息,但是,如果你在2007年寻找一辆红色雪佛兰卡马罗,你可以找到超过一辆。毕竟,汽车制造商向多个客户出售类似的汽车,这就是为什么注册车辆有单独识别每辆车辆的许可证号码。如果你看了一辆车有OFP857的许可证号码,你可以确定这个标准只会找到一辆车。 这是因为,根据法律,牌号号单独识别注册车辆。 在关系数据库中,这种数据被称为初级密钥。
Primary keys是单一列或列组中发现的唯一标识符,可以明确识别数据库表中的每个行。
- 主要密钥必须使用独特的值。如果主要密钥由多个列组成,则这些列中的值组合必须在整个表中是独一无二的。由于密钥的目的是单独识别每个行,所以它不能超过一次
- 主要密钥不能包含
NULL值 - 每个数据库表只能使用一个主要密钥
数据库引擎执行这些规则,因此如果主要密钥定义在表中,您可以依靠这些属性是真实的。
除了这些技术特性外,您还必须考虑数据的内容来决定哪种类型的数据是成为主密钥的最佳选择。 _Natural keys_是数据集中已经存在的标识符,而 surrogate keys是人工标识符。
有些数据结构有自然发生在数据集内的主要密钥,如汽车数据库中的许可证号码或美国公民目录中的社会保障号码,有时这些标识符不是一个值,而是一对或几个值的组合。例如,在当地城市的房屋目录中,只有街头名称或街头号码不能单独识别房子。
然而,通常数据不能被单一列或列小子集的值独特地描述,然后,人工主要密钥被创建,例如,使用一个序列的数字或随机生成的标识符,如 UUIDs。
在以下部分中,您将基于单个列或多个列创建自然密钥,并在自然密钥不是选项的表中生成替代密钥。
在单个列上创建一个主要密钥
在许多情况下,数据集自然包含一个单一的列,可以用来独特地识别表中的行。在这些情况下,您可以创建一个自然密钥来描述数据。
1[secondary_label Sample table]
2+---------------+-----------+------------+-------+------+
3| license_plate | brand | model | color | year |
4+---------------+-----------+------------+-------+------+
5| ABC123 | Ford | Mustang | Red | 2018 |
6| CES214 | Ford | Mustang | Red | 2018 |
7| DEF456 | Chevrolet | Camaro | Blue | 2016 |
8| GHI789 | Dodge | Challenger | Black | 2014 |
9+---------------+-----------+------------+-------+------+
第一行和第二行都描述了2018年的红色福特Mustang。仅使用汽车制造和车型,你将无法独特地识别汽车。在这两种情况下,许可证牌不同,为表中的每个行提供一个很好的唯一标识符。由于许可证牌号已经是数据的一部分,使用它作为主要钥匙将创建一个自然钥匙。
接下来,您将创建一个类似于上面的表,使用license_plate列作为主键和下列列列:
license_plate:本列包含许可证号码,由varchar数据类型表示品牌:本列包含使用varchar数据类型表示的汽车品牌,最多为 `50 个字符模型:本列包含使用varchar数据类型表示的汽车模型,最多为 `50 个字符颜色:本列包含使用varchar数据类型表示的颜色,最多为 `20 个字符。
若要创建汽车表,请执行以下 SQL 语句:
1CREATE 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);
「PRIMARY KEY」条款遵循「license_plate」数据类型定义. 处理基于单个列的主要密钥时,可以使用简化语法创建密钥,在列定义中写入「PRIMARY KEY」。
如果下列输出打印,则已创建表:
1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)
然后,通过运行以下 INSERT INTO 操作来加载上面的示例行:
1INSERT 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);
数据库将响应成功消息:
1[secondary_label Output]
2Query OK, 4 rows affected (0.010 sec)
3Records: 4 Duplicates: 0 Warnings: 0
现在,您可以使用 SELECT 语句验证新创建的表中包含预期数据和格式:
1SELECT * FROM cars;
输出将显示一个类似于该节开始的表:
1[secondary_label Output]
2+---------------+-----------+------------+-------+------+
3| license_plate | brand | model | color | year |
4+---------------+-----------+------------+-------+------+
5| ABC123 | Ford | Mustang | Red | 2018 |
6| CES214 | Ford | Mustang | Red | 2018 |
7| DEF456 | Chevrolet | Camaro | Blue | 2016 |
8| GHI789 | Dodge | Challenger | Black | 2014 |
9+---------------+-----------+------------+-------+------+
接下来,您可以验证主密钥的规则是否由数据库引擎保证。
1INSERT INTO cars VALUES ('DEF456', 'Jeep', 'Wrangler', 'Yellow', 2019);
MySQL 會以錯誤訊息回應,說「DEF456」許可證板會導致主要密钥的重複輸入:
1[secondary_label Output]
2ERROR 1062 (23000): Duplicate entry 'DEF456' for key 'cars.PRIMARY'
<$>[注] **注:**在封面下,主键与 独特索引实现,并与您可能手动为表中的其他列创建的索引共享许多属性。
您现在可以确定不允许重复许可证板。接下来,检查是否可以插入带空许可证板的汽车:
1INSERT INTO cars VALUES (NULL, 'Jeep', 'Wrangler', 'Yellow', 2019);
此時,資料庫會以另一個錯誤訊息回應:
1[secondary_label Output]
2ERROR 1048 (23000): Column 'license_plate' cannot be null
有了这些由数据库执行的两个规则,您可以确定license_plate将表中的每个行单独识别。
在下一节中,您将学习如何使用具有多个列的主要键。
在多个列上创建一个主要密钥
当一个列不足以识别表中的唯一行时,可以创建使用多个列的主要键。
例如,想象一个房屋注册表,无论是街头名称还是街头号码都不足以识别任何个别房屋:
1[secondary_label Sample table]
2+-------------------+---------------+-------------------+------+
3| street_name | street_number | house_owner | year |
4+-------------------+---------------+-------------------+------+
5| 5th Avenue | 100 | Bob Johnson | 2018 |
6| Broadway | 1500 | Jane Smith | 2016 |
7| Central Park West | 100 | John Doe | 2014 |
8| Central Park West | 200 | Tom Thompson | 2015 |
9| Lexington Avenue | 5001 | Samantha Davis | 2010 |
10| Park Avenue | 7000 | Michael Rodriguez | 2012 |
11+-------------------+---------------+-------------------+------+
街头名称中央公园西部在表中出现过多次,街头号码100也出现过多次,但是,没有可见的街头名称和街头号码的重复对,在这种情况下,虽然没有单一的列可以是主要钥匙,但这两个值的对可以用来独特地识别表中的每个行。
接下来,您将创建一个类似于上面显示的表,其中包含以下列:
street_name:此列包含房子所在的街道的名称,由varchar数据类型所表示,仅限于 `50 个字符street_number: 此列包含房屋的街道号,由varchar数据类型所表示。 此列可存储最多5 个字符。 它不使用数值int数据类型,因为某些街道号可能包含字母(例如200B`)。house_owner: 此列包含房屋所有者的名称,由varchar数据类型所表示,仅限于50 个字符( _*)年`: 此列包含房屋建造年份
这次,主键将使用street_name和street_number列,而不是单个列。
1CREATE 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);
此时,PRIMARY KEY条款出现在列定义下方,与前一个例子不同,PRIMARY KEY条款随后有两个列名单:street_name和street_number。
如果下列输出打印,则已创建表:
1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)
接下来,通过运行以下 INSERT INTO 操作来加载上示例行:
1INSERT 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);
数据库将响应成功消息:
1[secondary_label Output]
2Query OK, 6 rows affected (0.000 sec)
3Records: 6 Duplicates: 0 Warnings: 0
现在,您可以使用SELECT语句验证新创建的表中包含预期数据和格式:
1SELECT * FROM houses;
输出将显示一个类似于该节开始的表:
1[secondary_label Output]
2+-------------------+---------------+-------------------+------+
3| street_name | street_number | house_owner | year |
4+-------------------+---------------+-------------------+------+
5| 5th Avenue | 100 | Bob Johnson | 2018 |
6| Broadway | 1500 | Jane Smith | 2016 |
7| Central Park West | 100 | John Doe | 2014 |
8| Central Park West | 200 | Tom Thompson | 2015 |
9| Lexington Avenue | 5001 | Samantha Davis | 2010 |
10| Park Avenue | 7000 | Michael Rodriguez | 2012 |
11+-------------------+---------------+-------------------+------+
126 rows in set (0.000 sec)
现在,让我们检查数据库是否允许插入重复街道名称和街道号码的行,但限制重复的完整地址出现在表中。
1INSERT INTO houses VALUES ('Park Avenue', '8000', 'Emily Brown', 2011);
MySQL 會以成功訊息回應,因為地址「8000 Park Avenue」此前沒有出現在表中:
1[secondary_label Output]
2Query OK, 1 row affected (0.010 sec)
当你在8000 Main Street上添加房屋时,会出现类似的结果,重复街头号码:
1INSERT INTO houses VALUES ('Main Street', '8000', 'David Jones', 2009);
再次,这将正确插入新行,因为整个地址不会重复:
1[secondary_label Output]
2Query OK, 1 row affected (0.010 sec)
但是,请尝试在100 5th Avenue上添加另一个房子,使用下面的INSERT语句:
1INSERT INTO houses VALUES ('5th Avenue', '100', 'Josh Gordon', 2008);
数据库将以错误消息响应,通知您对值对5th Avenue和100的主要密钥有重复输入:
1[secondary_label Output]
2ERROR 1062 (23000): Duplicate entry '5th Avenue-100' for key 'houses.PRIMARY'
数据库正确执行主要密钥规则,密钥定义在一对列上,您可以确信由街头名称和街头号组成的完整地址不会被重复在表中。
在本节中,您创建了一个自然密钥,其中包含一对列,以便在房子表中单独识别每个行,但并不总是可以从数据集中提取主要密钥。
创建序列主要密钥
到目前为止,您已经使用样本数据集中的现有列创建了唯一的主要密钥,但在某些情况下,数据将不可避免地重复,防止任何列成为好的唯一标识符。
想象一下书俱乐部成员的列表 - 一个非正式的集会,任何人都可以加入,而不显示政府颁发的身份证。
1[secondary_label Sample table]
2+------------+-----------+
3| first_name | last_name |
4+------------+-----------+
5| John | Doe |
6| Jane | Smith |
7| Bob | Johnson |
8| Samantha | Davis |
9| Michael | Rodriguez |
10| Tom | Thompson |
11| Sara | Johnson |
12| David | Jones |
13| Jane | Smith |
14| Bob | Johnson |
15+------------+-----------+
Bob Johnson和Jane Smith的名字在表中重复使用,您需要使用额外的标识符来确定谁是谁,并且在表中无法以任何方式独特地识别行。
在关系数据库中,您可以通过使用生成的额外列存储,无事实标识符来做类似的事情,其唯一目的是单独分离表中的所有行,让我们称之为member_id。
但是,每当您想要将另一个图书俱乐部成员添加到数据库时,提出此类标识符将是一个负担,以解决此问题,MySQL提供了自动增加的数字列的功能,其中数据库会自动通过增量整数序列提供列值。
让我们创建一个类似于上面的表格的表格。您将添加一个额外的自动增量列(‘member_id’),以保持每个俱乐部成员的自动分配号码。
member_id:此列包含由int数据类型表示的自动增量数字标识符first_name:此列包含俱乐部成员的第一名,由varchar数据类型限定为50字符last_name:此列包含俱乐部成员的姓名,由varchar数据类型限定为50字符表示。
若要创建表,请执行以下 SQL 语句:
1CREATE TABLE club_members (
2 member_id int AUTO_INCREMENT PRIMARY KEY,
3 first_name varchar(50),
4 last_name varchar(50)
5);
虽然PRIMARY KEY条款在列类型定义后出现,就像一个单一列的主要密钥一样,在它之前出现了一个额外的属性:AUTO_INCREMENT。
<$>[注]
**注:**列定义的AUTO_INCREMENT属性是MySQL的特性。其他数据库通常提供类似的方法来生成序列密钥,但语法在引擎之间有所不同。
如果下列输出打印,则已创建表:
1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)
接下来,通过运行以下INSERT INTO操作来加载表中的示例行:
1INSERT INTO club_members (first_name, last_name) VALUES
2 ('John', 'Doe'),
3 ('Jane', 'Smith'),
4 ('Bob', 'Johnson'),
5 ('Samantha', 'Davis'),
6 ('Michael', 'Rodriguez'),
7 ('Tom', 'Thompson'),
8 ('Sara', 'Johnson'),
9 ('David', 'Jones'),
10 ('Jane', 'Smith'),
11 ('Bob', 'Johnson');
INSERT声明现在包含列表列名(first_name和last_name),这确保数据库知道member_id列未在数据集中提供,因此应该取代该列的默认值。
数据库将响应成功消息:
1[secondary_label Output]
2Query OK, 10 rows affected (0.002 sec)
3Records: 10 Duplicates: 0 Warnings: 0
使用SELECT语句来验证新创建表中的数据:
1SELECT * FROM club_members;
输出将显示一个类似于该节开始的表:
1[secondary_label Output]
2+-----------+------------+-----------+
3| member_id | first_name | last_name |
4+-----------+------------+-----------+
5| 1 | John | Doe |
6| 2 | Jane | Smith |
7| 3 | Bob | Johnson |
8| 4 | Samantha | Davis |
9| 5 | Michael | Rodriguez |
10| 6 | Tom | Thompson |
11| 7 | Sara | Johnson |
12| 8 | David | Jones |
13| 9 | Jane | Smith |
14| 10 | Bob | Johnson |
15+-----------+------------+-----------+
1610 rows in set (0.000 sec)
然而,这一次,member_id列出现在结果中,包含一个从1到10的数字序列.有了这个列,Jane Smith和Bob Johnson的重复行不再不可分辨,因为每个名字都与一个独特的标识符(member_id)相关联。
现在,让我们检查数据库是否允许将另一个Tom Thompson添加到俱乐部成员列表中:
1INSERT INTO club_members (first_name, last_name) VALUES ('Tom', 'Thompson');
MySQL 會以成功訊息回應:
1[secondary_label Output]
2Query OK, 1 row affected (0.009 sec)
若要检查数据库为新条目分配了哪个数字标识符,请再次执行SELECT查询:
1SELECT * FROM club_members;
输出中还有一个行:
1[secondary_label Output]
2+-----------+------------+-----------+
3| member_id | first_name | last_name |
4+-----------+------------+-----------+
5| 1 | John | Doe |
6| 2 | Jane | Smith |
7| 3 | Bob | Johnson |
8| 4 | Samantha | Davis |
9| 5 | Michael | Rodriguez |
10| 6 | Tom | Thompson |
11| 7 | Sara | Johnson |
12| 8 | David | Jones |
13| 9 | Jane | Smith |
14| 10 | Bob | Johnson |
15| 11 | Tom | Thompson |
16+-----------+------------+-----------+
1711 rows in set (0.000 sec)
通过数据库的AUTO_INCREMENT功能,在member_id列中自动分配一个新的行数字11。
如果您正在使用的数据没有主密钥的自然候选人,并且您不希望每次将新数据添加到数据库中时提供发明的标识符,则可以安全地依赖连续生成的标识符作为主密钥。
结论
通过遵循本指南,您了解了主键是什么,以及如何在MySQL中创建常见类型来识别数据库表中的独特行。
您可以使用主要密钥来进一步塑造数据库结构,确保数据行能够被独特识别。本教程只涵盖使用主要密钥的基本知识。 有关此事的更多信息,请参阅 MySQL 限制文档。
如果您想了解更多有关 SQL 语言的不同概念并使用它的工作,我们鼓励您查看 如何使用 SQL 系列中的其他指南。