如何在SQL中使用外键

后端之巅
1,022 阅读9分钟

作者选择了Apache软件基金会，作为Write for DOnations计划的一部分接受捐赠。

简介

当从事一个大型的SQL项目时，你必须在所有带有外键的表中保持数据的准确性和一致性。外键是关系数据库表中的一个列或一组列，它在两个表中的数据之间提供联系。在这个用例中，这就是参考完整性发挥作用的地方。例如，你可以有一个employees 表，其中有一个名为job_title_id 的列，它指向一个名为job_titles 的查询表。

另一个例子可以在电子商务数据库中展示，你可以在一个products 表中创建一个category_id 列，链接到一个父products_categories 表。

参考完整性确保所有的数据引用都是有效的，并防止不一致的条目或孤儿记录。参考完整性对于防止多用户数据库环境中无效数据的输入也很有用。

在本指南中，你将在你的数据库上用外键执行参照完整性。尽管本指南是在MySQL数据库上测试的，但它仍然可以在其他基于SQL的数据库中工作，只需改变一些语法。

前提条件

要完成本教程，你需要以下条件。

第1步 - 设置一个样本数据库和表

在这一步中，你将创建一个样本数据库并设置几个表。你还将插入一些样本数据，在整个指南中你将使用这些数据来处理外键。

首先，以非root用户身份连接到你的服务器。然后，执行下面的命令，登录到你的MySQL服务器。用你的非root账户的确切名称替换example_user 。

sudo mysql -u example_user -p

当出现提示时，输入你的MySQL服务器的非根用户账户密码，然后按ENTER 或RETURN ，继续。接下来，发出以下SQL命令，创建一个样本company_db 数据库。

CREATE DATABASE company_db;

确认以下输出，以确保数据库的创建没有错误。

OutputQuery OK, 1 row affected (0.01 sec)

一旦你成功地创建了数据库，输出中没有错误信息，应用SQLUSE 关键字，切换到新的company_db 数据库。

USE company_db;

你应该看到以下确认，表明你已经成功切换到company_db 数据库。

OutputDatabase changed

接下来，使用CREATE TABLE 命令建立一个job_titles 表。这个表可以作为你数据库中所有职位名称的查询表。job_title_id 是一个主键，使用BIGINT 数据类型唯一地识别你数据库中的每个职位名称，可以容纳多达2^63-1 记录。你使用AUTO_INCREMENT 关键字，让MySQL在你每次插入新的职位名称时自动分配连续的数字值。

在CREATE TABLE 命令中，包括一个job_title_name 列，该列存储一个人可读的职位名称的值。该列存储字符串值，最大长度为50 字符。你将用语法VARCHAR(50) 来定义这个数据类型。

在CREATE TABLE 命令之后，通过包括ENGINE = InnoDB 关键字，指示MySQL使用InnoDB 数据库引擎。这是一个事务就绪的通用存储引擎，它可以处理并发性，同时还能确保数据库应用的高可靠性和高性能。

执行下面的命令来创建job_titles 表。

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

在运行CREATE TABLE job_titles... 语句后，通过确认以下输出，确保你的命令已经成功完成。

OutputQuery OK, 0 rows affected (0.03 sec)

现在你有了一个查询表，用于查询你的例子公司中所有有效的职位。接下来，在job_titles 表中插入一些样本职位。

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

在每个命令之后，你应该得到以下确认信息。

OutputQuery OK, 1 row affected (0.00 sec)
...

现在你已经插入了可用的职位名称，使用MySQLSELECT 关键字查询job_titles 表以验证你的数据。

SELECT
job_title_id,
job_title_name            
FROM job_titles;

现在你应该看到一个列出所有可用职位的列表，如下图所示。

Output+--------------+--------------------+
| job_title_id | job_title_name     |
+--------------+--------------------+
|            1 | BRANCH MANAGER     |
|            2 | CLERK              |
|            3 | LEVEL 1 SUPERVISOR |
+--------------+--------------------+
3 rows in set (0.00 sec)

接下来，创建一个employees 表。这个表持有公司所有工作人员的记录。employees 表中的job_title_id 列指向job_titles 表中的同一列。你通过发布语句FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id) 来实现这一点。为了保持一致性，你要使用BIGINT 数据类型，这是你用于相关列的数据类型。

在下面的employees 表中，employees_id 是PRIMARY KEY ，当你插入新值时，你使用了AUTO_INCREMENT 关键字来生成新的employees_ids 。

你使用first_name 和last_name 文本字段来捕获雇员的姓名，最大长度为50 字符。这种数据类型对于电话号码来说也很完美。因此，VARCHAR(50) 数据类型应该适用于first_name,last_name, 和phone 领域。

为了提高从两个相互联系的表中检索数据时的速度，使用语句INDEX (job_title_id) 为job_title_id 列建立索引。同样，要确保包括关键字ENGINE = InnoDB ，以利用步骤1中概述的InnoDB 存储引擎的优势。

要创建employees 表，运行以下命令。

CREATE TABLE employees (
    employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    job_title_id BIGINT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(50),
    INDEX (job_title_id),
    FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id) 
) ENGINE = InnoDB;

确保你得到以下输出，确认你已经创建了该表。

OutputQuery OK, 0 rows affected (0.04 sec)

现在你已经为测试目的建立了正确的数据库和表，现在你将看看接下来在向表中插入数据时会发生什么。

第2步 - 插入无效的数据

在这一步中，你将向employees 表插入一些无主的记录。本例中的无主记录是指具有无效的job_title_ids 。从你的job_titles 表中，你只有3个有效的职位名称，如下图所示。

  1. BRANCH MANAGER
  2. CLERK
  3. LEVEL 1 SUPERVISOR

现在，通过运行下列INSERT 语句，尝试将一些无效的记录添加到employees 表中。

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

上述INSERT 语句应该全部失败，并显示以下错误，因为4,15, 和7 是无效的job_title_ids 。

OutputERROR 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`))
...

在下一步中，你将向employees 表输入有效数据，并检查命令是否成功。

第3步 - 插入有效数据

你已经看到，当表与外键相互连接时，参考完整性如何防止无效数据的输入。换句话说，使用外键可以使你的数据库保持一致的状态，即使不一定要在外部客户程序中编码该业务逻辑。

在这一步，你现在要插入有效的数据，看看插入是否会成功。运行以下命令。

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

由于你插入的是有效的job_title_ids ，现在INSERT 语句将会成功。在执行每个INSERT 命令后，你会收到以下输出。

OutputQuery OK, 1 row affected (0.00 sec)
...

现在，你会注意到，实现参照完整性是一种有用的方法，可以验证数据，并防止在处理相互关联的表时输入不存在的记录。同样，通过使用外键，你正在创建一个优化的数据库，允许你以一种有效的方式查询相互关联的数据。

例如，为了检索所有员工的记录，并拼出职位名称，针对employees 和job_titles 表运行以下JOIN 语句。

SELECT
employee_id,
employees.job_title_id,
job_titles.job_title_name,
first_name,
last_name,
phone      
FROM employees
LEFT JOIN job_titles
ON employees.job_title_id = job_titles.job_title_id;

在下面的输出中，每个员工的信息现在都显示在他们相关的角色/职位旁边。

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

正如你在上面的输出中看到的，你有一个BRANCH MANAGER ，三个CLERK ，和一个LEVEL 1 SUPERVISOR 。

当涉及到防止删除已经被链接的子表中的外键所引用的父记录时，外键也是非常好的。这里有几个现实生活中的例子，你可以应用这个。

  • 在一个电子商务网站中，当你在sales 表中有客户的活动订单时，你可以防止从customers 表中意外地删除客户的详细信息。

  • 在图书馆系统中，当学生在issued_books 表中有相关记录时，你可以防止从registers 表中删除该学生。

  • 在一个银行中，当客户已经在savings_accounts_transactions 表中做了一些存款/取款时，你可以使用外键的方法来避免从savings_accounts 表中删除记录。

同样地，你可以尝试删除你表中的数据。在你的命令行终端，从job_titles 表中删除一个位置。

DELETE FROM job_titles 
WHERE job_title_id = 1 ;

由于你已经在employees 表中插入了一条标题为BRANCH MANAGER 的记录，DELETE 语句将失败，并显示以下错误。

OutputERROR 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 表中添加一个新的角色。

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

一旦你成功地执行了该命令，你应该收到以下输出。

OutputQuery OK, 1 row affected (0.00 sec)
...

再次，查询job_titles 表，检查新职位的job_title_id 。

SELECT
job_title_id,
job_title_name            
FROM job_titles;

现在你应该看到一个列出所有可用职位的列表，如下图所示。CEO 角色的job_title_id 是4 。

Output+--------------+--------------------+
| job_title_id | job_title_name     |
+--------------+--------------------+
|            1 | BRANCH MANAGER     |
|            2 | CLERK              |
|            3 | LEVEL 1 SUPERVISOR |
|            4 | CEO                |
+--------------+--------------------+
4 rows in set (0.00 sec)

你现在在表中有4行。接下来，在向employees 表输入任何相关记录之前，删除新角色的job_title_id ，即4 。

DELETE FROM job_titles 
WHERE job_title_id = 4 ;

现在DELETE 语句应该成功了。

OutputQuery OK, 1 row affected (0.00 sec)

在完成上述所有测试后，没有任何错误，现在很明显，你的外键正在按预期工作。

总结

在本指南中，你已经建立了一个有相互关联的表的样本数据库，并实践了关系数据库管理系统中参考完整性的使用。你已经看到外键在验证和防止删除数据方面的重要性，否则会使数据库处于不一致的状态。在你的下一个数据库项目中使用本指南中的知识，以利用外键的优势。

要想对MySQL数据库进行更多的练习，请查看这些教程。

avatar
文章
阅读
粉丝