用 PostgreSQL 部分唯一索引保证每用户仅一个默认地址
问题场景
一个用户可以有多个收货地址,但业务要求同一用户最多只有一个默认地址。仅靠应用层先查再改,在并发下仍可能写入两条 is_default = true 的记录。PostgreSQL 的部分唯一索引可以把这条规则交给数据库保证:只对默认地址行建立唯一约束。
表结构与部分唯一索引
-- schema.sql
DROP TABLE IF EXISTS addresses;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id bigint PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE addresses (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
label text NOT NULL,
detail text NOT NULL,
is_default boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX uq_addresses_one_default_per_user
ON addresses (user_id)
WHERE is_default;
CREATE INDEX idx_addresses_user_id ON addresses (user_id);
WHERE is_default 让唯一索引只覆盖 is_default = true 的行,因此同一用户可以有任意多个非默认地址,但最多一个默认地址。is_default 建议声明为 NOT NULL,避免空值干扰业务判断。
最小可运行示例
准备测试数据:
INSERT INTO users (id, name) VALUES (1, 'user-a');
INSERT INTO addresses (user_id, label, detail, is_default)
VALUES (1, '家', '示例地址 A', true);
INSERT INTO addresses (user_id, label, detail, is_default)
VALUES (1, '公司', '示例地址 B', false);
把 id 为 2 的地址切换为默认地址。事务内先清除旧默认,再设置新默认:
BEGIN;
UPDATE addresses
SET is_default = false
WHERE user_id = 1 AND is_default;
UPDATE addresses
SET is_default = true
WHERE id = 2 AND user_id = 1;
COMMIT;
查询当前默认地址:
SELECT id, label, detail
FROM addresses
WHERE user_id = 1 AND is_default
ORDER BY id
LIMIT 1;
插入第二个默认地址会失败:
INSERT INTO addresses (user_id, label, detail, is_default)
VALUES (1, '备用', '示例地址 C', true);
此时数据库返回唯一约束冲突,SQLSTATE 为 23505。这就是部分唯一索引在兜底:无论应用层是否判断过,同一用户都不可能存在两条 is_default = true 的记录。
运行方法:先创建数据库,例如 createdb address_demo,再用 psql -d address_demo -f schema.sql 执行建表脚本,然后执行上面的示例 SQL。失败插入可单独用 psql -d address_demo -c '...' 运行,便于观察错误输出。
应用层事务与 23505
应用层仍应按“先旧后新”的顺序在同一个事务里执行,并捕获唯一冲突。下面是最小 Python 示例,使用 psycopg,DSN 为占位值:
import os
import psycopg
DSN = os.environ.get(
'DATABASE_URL',
'postgresql://postgres:password@localhost:5432/address_demo'
)
def set_default_address(user_id: int, address_id: int) -> None:
with psycopg.connect(DSN) as conn:
try:
with conn.transaction():
conn.execute(
'UPDATE addresses SET is_default = false '
'WHERE user_id = %s AND is_default',
(user_id,),
)
cur = conn.execute(
'UPDATE addresses SET is_default = true '
'WHERE id = %s AND user_id = %s',
(address_id, user_id),
)
if cur.rowcount != 1:
raise ValueError('目标地址不存在或不属于该用户')
except psycopg.errors.UniqueViolation:
raise RuntimeError('默认地址冲突,请重新读取后重试') from None
说明:conn.transaction() 正常退出时提交,异常时回滚;UniqueViolation 对应 SQLSTATE 23505。若目标地址不存在,rowcount 为 0,主动抛错让事务回滚,避免把旧默认地址清掉后没有新默认地址。
边界与限制
- 部分唯一索引只保证“最多一个默认地址”,不保证“至少一个默认地址”。如果业务要求每个用户必须恰好有一个默认地址,需要在应用层保证首次创建地址时写入默认地址,或增加额外校验/触发器,不能只依赖这个索引。
- 唯一索引默认立即检查,不是延迟约束。因此切换默认地址时,必须先执行清除旧默认的
UPDATE,再执行设置新默认的UPDATE;顺序反过来会在语句执行时触发冲突。 - 并发切换同一用户的默认地址时,两个事务仍可能竞争唯一索引。捕获 23505 后应重新读取当前状态再重试。若冲突较频繁,可先对
users中对应行执行SELECT ... FOR UPDATE,把同一用户的默认地址变更串行化,同时保留 23505 捕获作为兜底。 - 部分唯一索引只覆盖
is_default = true的行,非默认地址不受唯一性限制;仍建议为user_id建普通索引,便于按用户查询地址列表。 - 上述示例使用占位用户、地址和连接串;实际使用时替换为业务数据,不要把真实密钥写入代码。
- 点赞
- 收藏
- 关注作者
评论(0)