如何在 SQL 中使用外键

作者选择了 Apache Software Foundation作为 Write for Donations计划的一部分接受捐款。

介绍

当您在大型 SQL 项目中工作时,您必须在所有表中使用外部密钥保持数据的准确性和一致性。外部密钥是关系数据库表中的列或列组,该列提供两个表中的数据之间的链接。

另一个例子可以在电子商务数据库中展示,您可以在产品表中创建一个category_id列,该列链接到母级产品_类别表。

参考完整性确保所有数据引用是有效的,并防止不一致的条目或孤儿记录。

在本指南中,您将用外部密钥在您的数据库中强制执行参考完整性,尽管本指南在MySQL数据库上进行了测试,但它仍然可以在其他基于SQL的数据库中运行,仅有几次语法更改。

前提条件

要完成本教程,您将需要以下内容:

一个 Ubuntu 20.04 服务器以非根源的 sudo 用户和基本的防火墙为安全,请遵循 Ubuntu 20.04 初始服务器设置指南创建非根用户并启用防火墙。
MySQL 数据库服务器. 审查如何在 Ubuntu 20.04 上安装 MySQL(https://andsky.com/tech/tutorials/how-to-install-mysql-on-ubuntu-20-04) 服务器上的教程以设置和配置数据库服务器。

步骤 1 – 设置样本数据库和表

在此步骤中,您将创建样本数据库并设置几个表,您还将插入一些样本数据,用于在整个指南中使用外部密钥。

首先,以非根用户的身份连接到您的服务器,然后执行下面的命令登录您的MySQL服务器,以代替example_user为您的非根帐户的确切名称。

1sudo mysql -u example_user -p

当被提示时,输入您的MySQL服务器的非根用户帐户密码并按ENTER或RETURN来继续,然后发出以下SQL命令来创建一个样本company_db数据库:

1CREATE DATABASE company_db;

确认下面的输出,以确保数据库没有创建任何错误。

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

一旦您成功创建了数据库,输出中没有错误消息,请应用 SQL USE 关键字来切换到新的 company_db 数据库:

1USE company_db;

您应该看到以下确认,表明您已成功切换到company_db数据库:

1[secondary_label Output]
2Database changed

接下来,使用CREATE TABLE命令设置一个job_titles表. 此表作为数据库中所有可用的职位标题的搜索表。 job_title_id是一个主要密钥,使用BIGINT数据类型独特地识别数据库中的每个职位标题,可以容纳多达2^63-1的记录。您使用AUTO_INCREMENT关键字,允许MySQL每次插入新职位标题时自动分配序列数值。

在CREATE TABLE命令中,包括一个job_title_name列,该列存储了工作标题的可读值。

按照CREATE TABLE命令,指示MySQL使用InnoDB数据库引擎,包括ENGINE = InnoDB关键字。

运行以下命令来创建job_titles表:

1CREATE TABLE job_titles (
2    job_title_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    job_title_name VARCHAR(50)
4) ENGINE = InnoDB;

在运行CREATE TABLE job_titles...声明后,请通过确认以下输出来确保您的命令成功完成:

1[secondary_label Output]
2Query OK, 0 rows affected (0.03 sec)

你现在有一个搜索表的所有有效的职位可在你的示例公司. 接下来,插入一些示例的职位在job_titles表:

1INSERT INTO job_titles (job_title_name) VALUES ('BRANCH MANAGER');
2INSERT INTO job_titles (job_title_name) VALUES ('CLERK');
3INSERT INTO job_titles (job_title_name) VALUES ('LEVEL 1 SUPERVISOR');

每个命令后,您应该收到以下确认消息:

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)
3...

现在你已经插入了可用的职位标题,使用MySQL‘SELECT’关键字来查询‘job_titles’表来验证你的数据:

1SELECT
2job_title_id,
3job_title_name            
4FROM job_titles;

您现在应该看到列出的所有可用的职位列表如下所示:

1[secondary_label Output]
2+--------------+--------------------+
3| job_title_id | job_title_name     |
4+--------------+--------------------+
5|            1 | BRANCH MANAGER     |
6|            2 | CLERK              |
7|            3 | LEVEL 1 SUPERVISOR |
8+--------------+--------------------+
93 rows in set (0.00 sec)

接下来,创建一个员工表. 该表包含公司所有员工的记录。员工表中的job_title_id列指向job_titles表中的相同列。您通过发出声明FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id)来实现这一目标。

在下面的员工表中,员工_id是PRIMARY KEY,您在插入新值时使用了AUTO_INCREMENT关键字来生成新的员工_id。

您正在使用first_name和last_name文本字段捕捉员工的名称,最大长度为50字符,此数据类型也适合电话号码,因此VARCHAR(50)数据类型应适用于first_name,last_name和phone字段。

要提高从两个相互关联的表中获取数据的速度,请使用INDEX(job_title_id)语句来索引job_title_id列。

要创建员工表,运行以下命令:

1CREATE TABLE employees (
2    employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    job_title_id BIGINT NOT NULL,
4    first_name VARCHAR(50),
5    last_name VARCHAR(50),
6    phone VARCHAR(50),
7    INDEX (job_title_id),
8    FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id) 
9) ENGINE = InnoDB;

确保您收到以下输出,确认您已创建表:

1[secondary_label Output]
2Query OK, 0 rows affected (0.04 sec)

现在您已经设置了适当的数据库和表用于测试目的,现在您将查看在将数据插入表中时会发生什么。

步骤 2 – 输入不正确的数据

在此步骤中,您将将一些孤儿记录插入员工表中。在这种情况下,孤儿记录是具有无效job_title_ids的记录。

行业经理
行业经理
行业经理

现在,尝试将一些无效的记录添加到员工表中,运行以下INSERT陈述:

1INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (4, 'JOHN', 'DOE', '11111');
2INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (15, 'MARY', 'SMITH', '22222');
3INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (7, 'JANE', 'MIKE', '33333');

上面的输入语句都应该失败,并显示以下错误,因为4,15和7是无效的job_title_ids。

1[secondary_label Output]
2ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`))
3...

在下一步中,您将在员工表中输入有效数据,并检查命令是否成功。

步骤 3 – 输入有效的数据

您已经看到参考完整性如何防止在与外部密钥相连的表中输入无效数据,换句话说,使用外部密钥将数据库保持一致状态,即使不必在外部客户端应用程序中编码该业务逻辑。

在此步骤中,您现在将插入有效的数据,并查看插入是否成功。

1INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'PETER', 'SMITH', '55555');
2INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (1, 'JOHN', 'DOE', '11111');
3INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'STEVE', 'KIM', '66666');
4INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (3, 'MARY', 'ROE', '22222');
5INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'JANE', 'MIKE', '33333');

當您插入有效的「job_title_ids」,「INSERT」聲明將會成功。執行每個「INSERT」命令後,您將收到以下輸出:

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)
3...

到目前为止,您将注意到实施参考完整性是验证数据并防止在使用链接表时输入不存在的记录的一个有用的方法,此外,通过使用外部密钥,您正在创建一个优化的数据库,允许您以高效的方式查询链接数据。

例如,要获取所有职位名称的员工记录,请对员工和职位标题表执行以下JOIN声明。

 1SELECT
 2employee_id,
 3employees.job_title_id,
 4job_titles.job_title_name,
 5first_name,
 6last_name,
 7phone      
 8FROM employees
 9LEFT JOIN job_titles
10ON employees.job_title_id = job_titles.job_title_id;

在下面的输出中,每个员工的信息现在与其相关的角色/职位一起显示:

 1[secondary_label Output]
 2+-------------+--------------+--------------------+------------+-----------+-------+
 3| employee_id | job_title_id | job_title_name     | first_name | last_name | phone |
 4+-------------+--------------+--------------------+------------+-----------+-------+
 5|           5 |            1 | BRANCH MANAGER     | JOHN       | DOE       | 11111 |
 6|           4 |            2 | CLERK              | PETER      | SMITH     | 55555 |
 7|           6 |            2 | CLERK              | STEVE      | KIM       | 66666 |
 8|           8 |            2 | CLERK              | JANE       | MIKE      | 33333 |
 9|           7 |            3 | LEVEL 1 SUPERVISOR | MARY       | ROE       | 22222 |
10+-------------+--------------+--------------------+------------+-----------+-------+
115 rows in set (0.00 sec)

正如你在上面的输出中所看到的,你有一个行业经理,三个是CLERK,一个是Level 1 Supervisor。

外部密钥在防止已被链接的子表中的外部密钥引用的父母记录被删除时也非常好。

在电子商务网站中,您可以防止在销售表中对客户有活跃订单时,在客户表中意外删除客户信息。
在图书馆系统中,您可以防止学生在发行表中有相关记录时从注册表表中删除学生。

在命令行终端上,从job_titles表中删除一个单个位置:

1DELETE FROM job_titles 
2WHERE job_title_id = 1 ;

由于您已经在员工表中插入了一个名称为行业经理的记录,因此DELETE声明将失败,并显示以下错误:

1[secondary_label Output]
2ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`))

再次,将新角色添加到job_titles表中:

1INSERT INTO job_titles (job_title_name) VALUES ('CEO');

您应该在成功执行命令后收到以下输出。

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)
3...

再次,查询job_titles表以检查新职位的job_title_id:

1SELECT
2job_title_id,
3job_title_name            
4FROM job_titles;

您现在应该看到列出的所有可用职位的列表,如下所示,CEO角色有job_title_id的4:

 1[secondary_label Output]
 2+--------------+--------------------+
 3| job_title_id | job_title_name     |
 4+--------------+--------------------+
 5|            1 | BRANCH MANAGER     |
 6|            2 | CLERK              |
 7|            3 | LEVEL 1 SUPERVISOR |
 8|            4 | CEO                |
 9+--------------+--------------------+
104 rows in set (0.00 sec)

接下来,在输入任何关联的记录到员工表之前,删除job_title_id的新角色。

1DELETE FROM job_titles 
2WHERE job_title_id = 4 ;

DELETE声明现在应该成功。

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

在没有任何错误的情况下完成上述所有测试后,现在很明显你的外部密钥正在按预期工作。

结论

在本指南中,您已经设置了链接表的样本数据库,并在关系数据库管理系统中使用了参考完整性。您已经看到外部密钥在验证和防止数据删除方面的重要性,否则会使数据库处于不一致状态。

有关您的MySQL数据库的更多实践,请查看这些教程: