MySQL - 候选键

MySQL - 键：主键、备用键和外键

在关系型数据库中，键对于唯一标识记录和建立表之间的关系至关重要。它们是数据完整性和高效数据检索的基础。理解不同类型的键——候选键、主键、备用键和外键——对于设计健壮可靠的数据库模式至关重要。

候选键：唯一性之池

一个 候选键 是表中可以唯一标识每一行的一列或一组列。一个表可以有多个候选键。一个键要成为候选键，必须具备两个属性：

• 唯一性： 它必须包含每行的唯一值。
• 不可约性： 如果键是复合键（多列），则其列的任何子集都不能唯一标识一行。

可以将候选键视为所有可能成为表主要标识符的潜在候选。

主键：被选中者

一个 主键 是由数据库设计者选定作为表主要、官方标识符的候选键。它有一些严格的规则：

• 它必须唯一标识表中的每条记录。
• 它不能包含 NULL 值。
• 一个表只能有一个主键。

在 MySQL 中，你可以使用 PRIMARY KEY 约束来定义它。它会自动创建一个聚簇索引，该索引物理地对表数据进行排序，使得通过此键进行查找非常快速。

备用键：候补者

一个 备用键 是未被选作主键的候选键。虽然它不是 主要 标识符，但它仍然唯一标识每一行。实际上，备用键在 MySQL 中通过 UNIQUE 约束实现。

备用键的特点（由 UNIQUE 约束强制执行）：

• 它确保列中（或列组合中）的所有值都是唯一的。
• 一个表可以有多个备用键（多个 UNIQUE 约束）。
• 与主键不同，UNIQUE 约束默认可以允许一个 NULL 值。

外键：表之间的链接

一个 外键 是一个表中的一列（或一组列），它引用另一个表的主键。它是将表连接在一起并强制执行参照完整性的机制。这意味着你不能向子表（包含外键的表）添加记录，除非父表（包含主键的表）中存在对应的记录。

外键还可以为 ON UPDATE 和 ON DELETE 事件定义操作，例如 CASCADE（如果父行被删除，则子行也随之删除）或 SET NULL（将外键设置为 NULL）。

键的可视化

                  +-----------------------+
                  |      候选键           |
                  |   (所有唯一列)        |
                  |                       |
                  |  +-----------------+  |
                  |  |    主键         |  |  (例如，employee_id)
                  |  +-----------------+  |
                  |                       |
                  |  +-----------------+  |
                  |  |   备用键 1      |  |  (例如，email)
                  |  +-----------------+  |
                  |                       |
                  |  +-----------------+  |
                  |  |   备用键 2      |  |  (例如，ssn)
                  |  +-----------------+  |
                  |                       |
                  +-----------------------+

实际示例：设计一个 Employees 表

让我们设计一个员工表。我们确定了几个潜在的候选键：employee_id、email 和 ssn（社会安全号码）。所有这些都是唯一的。

• 我们选择 employee_id 作为主键。它是一个数字代理键，稳定且高效。
• email 和 ssn 成为我们的备用键。我们使用 UNIQUE 约束来强制其唯一性。

CREATE TABLE Employees (
    employee_id INT AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    ssn VARCHAR(11) NOT NULL, -- 社会安全号码
    department_id INT,

    -- 定义主键
    PRIMARY KEY (employee_id),

    -- 定义备用键
    UNIQUE (email),
    UNIQUE (ssn)
);

-- 现在，让我们创建一个部门表并进行链接
CREATE TABLE Departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- 向 Employees 表添加外键约束
ALTER TABLE Employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES Departments(department_id)
ON DELETE SET NULL; -- 如果部门被删除，将员工的部门设置为 NULL

验证

让我们使用 DESCRIBE 命令检查 Employees 表的结构。

DESCRIBE Employees;

输出清晰地显示了我们的键结构。

字段	类型	允许空	键	默认值	额外信息
employee_id	int	NO	PRI	NULL	auto_increment
first_name	varchar(100)	NO		NULL
last_name	varchar(100)	NO		NULL
email	varchar(255)	NO	UNI	NULL
ssn	varchar(11)	NO	UNI	NULL
department_id	int	YES	MUL	NULL

键列解释： PRI 代表主键（Primary Key），UNI 代表唯一（Unique，我们的备用键），MUL 代表多重（Multiple，表示它是一个索引列，在此例中是外键）。

键选择的最佳实践

• 优先使用代理键： 使用 AUTO_INCREMENT 整型（employee_id）作为主键。这称为代理键。它稳定（永不改变）、简单且对连接高效。
• 避免将自然键用作主键： “自然键”是具有业务意义的键（例如电子邮件地址或 SSN）。这些键可能会发生变化（例如，一个人更改了电子邮件），如果它是一个在外键关系中使用的主键，则会造成重大问题。应将其用作备用键。
• 强制所有唯一性： 如果一列必须是唯一的（例如 email），请始终添加 UNIQUE 约束。不要依赖应用程序逻辑来强制执行此操作。这可以保证数据库级别的数据完整性。
• 谨慎对待外键操作： 仔细选择你的 ON DELETE 和 ON UPDATE 操作。RESTRICT（默认值）最安全。CASCADE 功能强大，但可能导致意外的大量删除。如果关系是可选的，SET NULL 是一个很好的折衷方案。