数据库设计中,主键（Primary Key）是一个至关重要的概念，它用于唯一标识表中的每一行记录，正确设置主键不仅能确保数据的完整性和一致性，还能提高查询效率并优化数据库性能，以下是关于如何在不同类型的关系型数据库管理系统（如MySQL、PostgreSQL、SQL Server等）中设置主键的详细说明。
什么是主键？
主键是表中的一个或多个字段的组合,具有以下特性：
- 唯一性：每个值必须唯一，不能重复；
- 非空性：不允许为NULL；
- 不可变性：一旦插入后不能修改或删除（除非先移除整个行）。
通常建议使用自动递增的数字类型作为单列主键（例如自增ID），但也可以根据业务需求选择复合主键（多列组合）。
为什么需要设置主键？
| 作用 | 解释 |
|---|---|
| 确保数据唯一性 | 防止出现两条完全相同的记录 |
| 加速关联查询 | 外键依赖主键建立表间关系，提升JOIN操作的速度 |
| 支持索引优化 | 默认创建唯一索引，加快检索速度 |
| 维护参照完整性 | 通过外键约束保证相关数据的一致性 |
| 便于排序与分组 | 按主键排序时结果稳定且高效 |
不同数据库系统的实现方法
MySQL / MariaDB
✅ 创建表时直接定义
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT, -自增列适合做主键
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
PRIMARY KEY (id) -显式声明主键
);
⚠️ 如果未指定AUTO_INCREMENT，需手动管理数值分配；若省略PRIMARY KEY子句，则第一个NOT NULL且UNIQUE的列会自动成为候选键。
✅ 后期添加已有表的主键
ALTER TABLE orders ADD PRIMARY KEY (order_number); -或修改现有结构：ALTER TABLE products CHANGE COLUMN old_col new_col INT NOT NULL FIRST;
📌 注意：当表中已有数据时，新增的主键必须满足唯一性和非空条件，否则会报错，此时可能需要先清理重复项再执行此操作。
PostgreSQL
PostgreSQL语法与标准SQL更贴近,支持类似逻辑但细节略有差异：
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY, -SERIAL相当于自增序列+整数类型
name TEXT NOT NULL,
hire_date TIMESTAMP DEFAULT NOW()
);
💡 使用SERIAL会自动创建一个名为employees_id_seq的顺序对象来生成连续编号，对于复合主键，可这样写：
CREATE TABLE bookings (
event_id INT REFERENCES events(event_id),
user_id INT REFERENCES users(user_id),
created_at TIMESTAMP,
PRIMARY KEY (event_id, user_id) -两列共同构成复合主键
);
SQL Server
T-SQL提供了明确的关键字控制：
CREATE TABLE customers (
customer_id INT IDENTITY(1,1) PRIMARY KEY, -IDENTITY表示自增起始值为1步长为1
fullname NVARCHAR(100),
join_date DATETIME CONSTRAINT df_join_date DEFAULT GETDATE()
);
🔍 IDENTITY属性只能应用于单个列，不支持多列自增，若需复合主键，需自行保证插入顺序的唯一性。
SQLite
轻量级嵌入式数据库同样遵循ANSI标准：
CREATE TABLE logs (
log_id INTEGER PRIMARY KEY AUTOINCREMENT, -整型主键自动递增
message TEXT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
🔧 SQLite会自动为主键创建名为sqlite_sequence的内部表跟踪下一个可用ID。
最佳实践建议
| 场景 | 推荐方案 | 原因说明 |
|---|---|---|
| 新项目初始化建表 | 优先选择自增数字型单列主键（如id BIGINT UNSIGNED AUTO_INCREMENT） |
简单高效，兼容绝大多数ORM框架 |
| 已有遗留系统改造 | 评估现有数据的冗余度后逐步迁移至规范化结构 | 避免因强制约束导致大规模锁表影响线上服务 |
| 高并发写入场景 | 考虑UUID作为替代方案（如CHAR(36)存储GUID字符串） |
分布式环境下减少竞争冲突，但牺牲了局部有序性和存储空间 |
| 历史归档表管理 | 禁用外键约束以降低维护成本 | 老旧数据不再参与事务一致性校验时可适当放宽限制 |
常见问题排查指南
遇到以下错误时可以参考对应解决方案：
- ErrCode: 1594 (MySQL): “Table has more than one primary key candidate” → 检查是否存在多个NOT NULL且UNIQUE的列试图抢占主键位置。
- Violates unique constraint: 插入重复值触发异常 → 使用
SELECT DISTINCT column FROM table;定位冲突条目。 - Cannot drop index needed for foreign key: 删除主键前需先移除关联的外键约束。
FAQs
Q1: 如果不小心选错了主键怎么办？能否更换？
答：可以修改，但过程较复杂，例如在MySQL中需分三步：①删除原有主键（ALTER TABLE table_name DROP PRIMARY KEY;）；②添加新主键（ALTER TABLE table_name ADD PRIMARY KEY (new_column);）；③更新依赖该表的其他表的外键关系，建议备份数据并在低峰期操作。
Q2: 是否所有表都必须有主键？有没有例外情况？
答：理论上不需要强制要求，但从工程角度看，缺乏主键会导致以下风险：①无法有效建立外键关联；②难以进行精准更新/删除；③全表扫描性能低下，临时表或中间过渡表可暂不设置，但生产环境的持久化存储应尽量遵循规范，某些NoSQL数据库（如MongoDB）采用文档模型，其“主键”概念与传统关系型数据库不同，此处讨论范围限定于关系型
原创文章，发布者：酷盾叔，转转请注明出处：https://www.kd.cn/ask/79365.html
