用 PostgreSQL 部分唯一索引保证每用户仅一个默认地址

举报
yd_240403232 发表于 2026/11/18 08:06:47 2026/11/18
【摘要】 介绍用 PostgreSQL 部分唯一索引约束每个用户最多一个默认地址,给出建表、在事务内切换默认地址、捕获 23505 冲突以及插入第二个默认地址失败和查询默认地址的验证 SQL,并说明并发与至少一个默认地址等边界。

问题场景

一个用户可以有多个收货地址,但业务要求同一用户最多只有一个默认地址。仅靠应用层先查再改,在并发下仍可能写入两条 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,主动抛错让事务回滚,避免把旧默认地址清掉后没有新默认地址。

边界与限制

  1. 部分唯一索引只保证“最多一个默认地址”,不保证“至少一个默认地址”。如果业务要求每个用户必须恰好有一个默认地址,需要在应用层保证首次创建地址时写入默认地址,或增加额外校验/触发器,不能只依赖这个索引。
  2. 唯一索引默认立即检查,不是延迟约束。因此切换默认地址时,必须先执行清除旧默认的 UPDATE,再执行设置新默认的 UPDATE;顺序反过来会在语句执行时触发冲突。
  3. 并发切换同一用户的默认地址时,两个事务仍可能竞争唯一索引。捕获 23505 后应重新读取当前状态再重试。若冲突较频繁,可先对 users 中对应行执行 SELECT ... FOR UPDATE,把同一用户的默认地址变更串行化,同时保留 23505 捕获作为兜底。
  4. 部分唯一索引只覆盖 is_default = true 的行,非默认地址不受唯一性限制;仍建议为 user_id 建普通索引,便于按用户查询地址列表。
  5. 上述示例使用占位用户、地址和连接串;实际使用时替换为业务数据,不要把真实密钥写入代码。
【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。