sql-primary-key

SQL 主键

什么是 SQL 主键？

SQL 主键（Primary Key）是一种约束，它能唯一标识数据库表中的每一条记录。它就像人的社会安全号码（Social Security Number）或汽车的车辆识别号（VIN）一样，是某一行的唯一标识符。主键确保你可以可靠且唯一地引用表中的任何特定行。

除了标识作用，主键对数据库性能也至关重要。它们会自动创建一个索引（index），这会显著加快数据检索操作的速度。主键还构成了表之间关系的基础，这将在我们讨论外键（Foreign Key）时看到。

主键的关键属性

主键本质上是以下两种其他约束的组合：

• UNIQUE（唯一）：主键列中的所有值都必须是不同的。不允许两行拥有相同的主键值。
• NOT NULL（非空）：主键列不能包含 NULL 值。每一行都必须有一个主键值。
• 每表一个键：一个表只能有一个主键，尽管该主键可以由一个或多个列组成（这被称为复合键，composite key）。

选择主键：自然键与代理键

在选择主键时，你有两个主要选项：

• 自然键（Natural Key）：由现实世界中已经存在且唯一的属性形成的键。例如，用户的电子邮件地址或国家的 ISO 代码。虽然看似方便，但如果自然键发生变化（例如，用户更改了电子邮件地址），它们可能会带来问题。
• 代理键（Surrogate Key）：一种没有业务含义，纯粹为了充当主键而创建的键。这通常是一个自增整数或一个通用唯一标识符（UUID）。代理键是大多数情况下推荐的最佳实践，因为它们稳定且在表内保证唯一。

创建主键

在表创建期间（推荐）

定义主键的最佳时机是首次创建表时。对于自增代理键，不同数据库系统的语法略有不同。

语法：PostgreSQL（使用 SERIAL）

CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

语法：MySQL（使用 AUTO_INCREMENT）

CREATE TABLE Customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

语法：SQL Server（使用 IDENTITY）

CREATE TABLE Customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) UNIQUE NOT NULL
);

你也可以单独定义约束，这对于命名约束和创建复合键很有用。

CREATE TABLE Customers (
    customer_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY (customer_id)
);

在现有表上

如果一个表在创建时没有定义主键，你可以使用 ALTER TABLE 语句添加一个。只有在你选择的列已包含唯一且非空的值时，此操作才会成功。

语法

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

示例

假设我们有一个没有主键的 Products 表。

-- 仅当 'product_sku' 包含唯一且非空值时此操作才有效。
ALTER TABLE Products
ADD CONSTRAINT pk_products PRIMARY KEY (product_sku);

复合主键

当单个列不足以唯一标识一行时，你可以使用复合主键（composite primary key），它由两个或更多列组成。一个经典的例子是多对多关系中的连接表。

示例

想象一个将订单与产品关联的表。order_id 和 product_id 的组合必须是唯一的。

CREATE TABLE Order_Items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    -- 外键约束通常也会在此处添加
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);

删除主键

删除主键并不常见，但可以使用 ALTER TABLE 实现。如果约束是显式命名的，则必须使用该名称。如果未命名，则语法可能因数据库而异。

语法（带命名约束）

ALTER TABLE Customers
DROP CONSTRAINT pk_customers;

语法（MySQL 不带命名约束）

ALTER TABLE Customers
DROP PRIMARY KEY;

警告：删除主键可能会产生严重后果，特别是当其他表通过外键引用它时。它还会移除相关索引带来的性能优势。

常见错误和最佳实践

• 错误：“Duplicate entry” 或 “UNIQUE constraint failed”。当你尝试插入一个主键值已存在的行时，会发生此错误。
• 错误：“Column cannot be null”。当你尝试插入一行而未提供主键列的值时，会发生此错误。
• 最佳实践：优先使用代理键（如 SERIAL 或 AUTO_INCREMENT），而非自然键。
• 最佳实践：始终命名你的约束（CONSTRAINT pk_customers PRIMARY KEY(...)）。这会在未来使管理和调试它们变得更加容易。