在 PostgreSQL 中，主键和唯一约束是两种最重要的数据完整性约束。它们都能保证数据的唯一性，但用途和行为有所不同。下面详细讲解如何在 PostgreSQL 表中添加这些约束。

一、约束的概念

约束类型 关键字 作用 是否创建索引 允许 NULL
主键 PRIMARY KEY 唯一标识一行记录 是 ❌ 不允许
唯一约束 UNIQUE 保证列值唯一 是 ✅ 允许一个 NULL

二、添加主键约束

1. 创建表时定义主键

单列主键：

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

组合主键（多列）：

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

命名主键约束：

CREATE TABLE users (
    user_id SERIAL,
    username VARCHAR(50) NOT NULL,
    CONSTRAINT pk_users_id PRIMARY KEY (user_id)
);

2. 为已存在的表添加主键

基本语法：

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

示例：

-- 为已存在的 users 表添加主键
ALTER TABLE users ADD PRIMARY KEY (user_id);

-- 组合主键
ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);

自定义约束名：

ALTER TABLE users 
ADD CONSTRAINT pk_users PRIMARY KEY (user_id);

3. 使用现有索引创建主键

-- 先创建唯一索引
CREATE UNIQUE INDEX idx_users_id ON users (user_id);

-- 再将索引转为主键
ALTER TABLE users ADD PRIMARY KEY USING INDEX idx_users_id;

三、添加唯一约束

1. 创建表时定义唯一约束

单列唯一：

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,  -- 方式1：简写
    email VARCHAR(100) CONSTRAINT users_email_unique UNIQUE  -- 方式2：命名
);

多列唯一：

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    UNIQUE (first_name, last_name)  -- 组合唯一
);

2. 为已存在的表添加唯一约束

方式一：使用 ALTER TABLE（推荐，会创建约束对象）

-- 单列唯一
ALTER TABLE users ADD UNIQUE (email);

-- 命名约束
ALTER TABLE users 
ADD CONSTRAINT users_email_unique UNIQUE (email);

-- 多列唯一
ALTER TABLE users 
ADD CONSTRAINT users_name_unique UNIQUE (first_name, last_name);

方式二：直接创建唯一索引（不创建约束对象）

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- 多列唯一索引
CREATE UNIQUE INDEX idx_users_name ON users (first_name, last_name);

四、主键与唯一索引的完整示例

实际业务场景：用户表设计

-- 创建用户表
CREATE TABLE users (
    user_id SERIAL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    id_card VARCHAR(18),
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW()
);

-- 添加主键
ALTER TABLE users 
ADD CONSTRAINT pk_users PRIMARY KEY (user_id);

-- 添加唯一约束
ALTER TABLE users 
ADD CONSTRAINT users_username_unique UNIQUE (username);

ALTER TABLE users 
ADD CONSTRAINT users_email_unique UNIQUE (email);

ALTER TABLE users 
ADD CONSTRAINT users_phone_unique UNIQUE (phone);

ALTER TABLE users 
ADD CONSTRAINT users_id_card_unique UNIQUE (id_card);

-- 查看表结构
\d users

订单系统示例

-- 订单表
CREATE TABLE orders (
    order_id SERIAL,
    order_no VARCHAR(50) NOT NULL,
    user_id INT NOT NULL,
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 订单明细表
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- 添加主键
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_id);
ALTER TABLE orders ADD CONSTRAINT orders_order_no_unique UNIQUE (order_no);

ALTER TABLE order_items 
ADD CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id);

-- 添加外键约束
ALTER TABLE order_items 
ADD CONSTRAINT fk_order_items_orders 
FOREIGN KEY (order_id) REFERENCES orders(order_id);

五、处理添加约束时的常见问题

问题1：添加主键时表中有重复数据

-- 1. 找出重复数据
SELECT user_id, COUNT(*) 
FROM users 
GROUP BY user_id 
HAVING COUNT(*) > 1;

-- 2. 删除重复数据（保留一条）
DELETE FROM users a 
USING users b 
WHERE a.user_id = b.user_id 
AND a.ctid < b.ctid;  -- ctid 是行物理位置，保留最新的一条

-- 3. 添加主键
ALTER TABLE users ADD PRIMARY KEY (user_id);

问题2：添加唯一约束时有重复值

-- 1. 找出重复的 email
SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

-- 2. 处理重复数据（例如：只保留最新的记录）
DELETE FROM users 
WHERE (email, created_at) NOT IN (
    SELECT email, MAX(created_at)
    FROM users
    GROUP BY email
);

-- 3. 添加唯一约束
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

问题3：大表添加约束的性能考虑

-- 对大表，可以先创建索引，再用索引创建约束
-- 这样可以更好地控制锁表时间

-- 先并发创建唯一索引（不阻塞读写）
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_tmp 
ON users (email);

-- 再用索引创建约束
ALTER TABLE users 
ADD CONSTRAINT users_email_unique 
UNIQUE USING INDEX idx_users_email_tmp;

六、约束的管理和维护

查看约束信息

-- 查看表的所有约束
SELECT conname AS constraint_name,
       contype AS constraint_type,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

-- contype 说明：
-- p = 主键约束
-- u = 唯一约束
-- f = 外键约束
-- c = 检查约束

查看索引信息

-- 查看表的所有索引
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'users';

删除约束

-- 删除主键约束
ALTER TABLE users DROP CONSTRAINT pk_users;

-- 删除唯一约束
ALTER TABLE users DROP CONSTRAINT users_email_unique;

-- 删除唯一索引（如果是直接创建的索引）
DROP INDEX idx_users_email;

临时禁用约束

PostgreSQL 不支持直接禁用约束，但可以通过修改约束属性实现类似效果：

-- 将约束设置为可延迟，并在事务中临时推迟检查
ALTER TABLE users 
ALTER CONSTRAINT users_email_unique 
DEFERRABLE INITIALLY DEFERRED;

-- 在事务中插入数据，提交时才检查唯一性
BEGIN;
SET CONSTRAINTS users_email_unique DEFERRED;
INSERT INTO users (email) VALUES ('test@example.com');
INSERT INTO users (email) VALUES ('test@example.com'); -- 临时重复没问题
COMMIT; -- 提交时如果还有重复才会报错

七、最佳实践总结

设计原则

  1. 每个表都应该有主键 - 通常使用自增列（SERIAL 或 IDENTITY）
  2. 业务唯一键用 UNIQUE 约束 - 便于管理和文档化
  3. 组合主键要谨慎 - 除非确实需要，否则建议用单列代理键 + 唯一约束
  4. 索引命名规范 - 使用有意义的名称，便于维护
    • 主键：pk_表名
    • 唯一约束：表名_列名_unique
    • 唯一索引：idx_表名_列名_unique

命名规范示例

CREATE TABLE products (
    product_id SERIAL,
    product_code VARCHAR(50) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    CONSTRAINT pk_products PRIMARY KEY (product_id),
    CONSTRAINT products_code_unique UNIQUE (product_code)
);

CREATE INDEX idx_products_name ON products (product_name);

性能考虑

  1. 大表添加约束 - 使用 CONCURRENTLY 或先用索引再建约束
  2. 维护窗口 - 约束添加会锁表，选择业务低峰期操作
  3. 定期维护 - 使用 REINDEX 重建索引，提升性能

如果你有具体的表结构或业务需求，欢迎告诉我，我可以帮你设计合适的约束方案。

# postgresql # linux # 服务器 # 架构 # 算法
Logo
DeepSeek技术社区

欢迎加入DeepSeek 技术社区。在这里，你可以找到志同道合的朋友，共同探索AI技术的奥秘。

更多推荐

cover

DeepSeek RAG 查询缓存命中率优化：如何从 30% 提升到 80% 的工程实践

avatar DeepSeek技术社区
cover

DeepSeek-V4 服务健康检查：为什么你的离线评测全绿，上线却崩了？

avatar DeepSeek技术社区
cover

DeepSeek输出护栏实战：如何用结构化JSON抵御越狱与幻觉

avatar DeepSeek技术社区