sql-alternate-key

SQL - 备用键

理解数据库中的键

要理解备用键 (Alternate Keys)，我们必须首先理解关系数据库理论中键的层次结构：

候选键 (Candidate Key)： 一列或一组列，可以唯一标识表中的每一行。一个表可以有多个候选键。根据定义，候选键不能包含 NULL 值。
主键 (Primary Key)： 数据库设计者选择作为表主要标识符的一个候选键。它是标识行最重要的键。
备用键 (Alternate Key)： 任何未被选为主键的候选键。它们是表的辅助唯一标识符。

什么是备用键？

备用键是一列或一组列，它提供了唯一标识表中行的替代方法。可以把它看作是主键选举中的“亚军”。

例如，考虑一个 Employees 表。您可能有几列可以唯一标识一个员工：

EmployeeID（一个自增数字）
SSN（社会安全号）
Username（公司登录名）

这三者都是候选键。如果您选择 EmployeeID 作为主键，那么 SSN 和 Username 就成为备用键。

SQL 中没有特殊的 ALTERNATE KEY 语法。备用键是一个概念性术语。您通过使用约束强制其唯一性和非空性来实现它。

如何实现备用键

要正确实现备用键并确保它能够可靠地标识一行，您必须使用 SQL 约束强制执行两个属性：

唯一性： 使用 UNIQUE 约束来确保没有两行在该键中具有相同的值。
非空性： 使用 NOT NULL 约束来确保键始终具有值。

一列（或一组列）同时具有 UNIQUE 和 NOT NULL 约束，实际上就是备用键。

外键与备用键

SQL 中一个强大且经常被忽视的特性是，FOREIGN KEY 可以引用另一张表中的任何 UNIQUE 键，而不仅仅是主键。这意味着您可以基于备用键创建外键关系，这在与使用不同自然标识符的系统集成时非常有用。

示例：定义和使用备用键

让我们创建一个 Users 表，其中 UserID 是主键，Email 和 Username 都是备用键。请注意 PRIMARY KEY、UNIQUE 和 NOT NULL 的使用。

CREATE TABLE Users (
    UserID INT GENERATED ALWAYS AS IDENTITY, -- PostgreSQL 现代自增语法
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    FullName VARCHAR(100),

    -- 1. 定义主键
    CONSTRAINT PK_Users PRIMARY KEY (UserID),

    -- 2. 定义备用键
    CONSTRAINT UQ_Users_Username UNIQUE (Username),
    CONSTRAINT UQ_Users_Email UNIQUE (Email)
);

-- 注意：在 MySQL 中，您可能对 UserID 使用 'INT AUTO_INCREMENT PRIMARY KEY'。

在此模式中：

候选键 (Candidate Keys)： (UserID)、(Username)、(Email)
主键 (Primary Key)： (UserID)
备用键 (Alternate Keys)： (Username)、(Email)

现在，让我们创建一个 Posts 表，它通过 Username 这一备用键引用 Users 表。

CREATE TABLE Posts (
    PostID INT PRIMARY KEY,
    AuthorUsername VARCHAR(50) NOT NULL,
    Title VARCHAR(200),
    Content TEXT,

    -- 此外键指向 Users 表中的一个备用键
    CONSTRAINT FK_Posts_Users FOREIGN KEY (AuthorUsername) REFERENCES Users (Username)
);

主键与备用键：选择很重要

当您有多个候选键时，如何选择主键？这是一个基本的数据库设计决策。

代理键 (例如，`UserID`)

代理键是一种没有业务含义的人工键，通常是自增整数。这是最常见且通常是最佳实践的方法。

优点： 稳定（永不改变），小巧（对连接高效），简单。
缺点： 没有实际意义。

自然键 (例如，`Email`、`SSN`)

自然键是同时也是真实世界属性的候选键。

优点： 具有业务含义，在某些查询中可以避免 JOIN 操作。
缺点： 可能会改变（例如，用户更改其电子邮件），可能很长（对连接效率低），可能涉及隐私问题（例如，SSN）。