PostgreSQL如何实现分类表中仅存在唯一默认行的需求？

核心实现思路 #

组合使用部分唯一索引和触发器即可实现需求，无需额外建表，全程在现有分类表上完成约束配置。

具体实现步骤 #

1. 新增默认标记字段 #

给分类表增加布尔类型的is_default字段，默认值设为NULL：

ALTER TABLE category ADD COLUMN is_default BOOLEAN DEFAULT NULL;

2. 加部分唯一索引保证仅一条默认记录 #

利用PostgreSQL的部分唯一索引特性，仅对is_default = true的行做全局唯一性校验，从根源上避免出现多条默认分类：

CREATE UNIQUE INDEX idx_category_only_one_default
ON category ((1))
WHERE is_default = true;

索引列使用固定值1，是为了让所有满足is_default = true的行的索引值完全相同，从而触发唯一约束，和直接用is_default作为索引列效果一致，写法更简洁。

3. 加触发器实现默认分类自动切换 #

创建行级触发器，当某一行被设置为默认时，自动将其余行的默认标记清空，无需业务代码手动处理：

-- 触发器函数
CREATE OR REPLACE FUNCTION func_reset_other_default()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.is_default = true THEN
    UPDATE category
    SET is_default = NULL
    WHERE id != NEW.id AND is_default = true;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到分类表
CREATE TRIGGER trg_category_switch_default
BEFORE INSERT OR UPDATE OF is_default ON category
FOR EACH ROW EXECUTE FUNCTION func_reset_other_default();

4. 加校验触发器保证始终存在默认记录 #

新增语句级触发器，防止误操作把最后一条默认分类的标记清空或者删除默认分类：

-- 校验函数
CREATE OR REPLACE FUNCTION func_ensure_default_exists()
RETURNS TRIGGER AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM category WHERE is_default = true) THEN
    RAISE EXCEPTION '操作失败：分类表必须保留至少一个默认分类';
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 绑定校验触发器
CREATE TRIGGER trg_category_ensure_default
AFTER UPDATE OF is_default OR DELETE ON category
FOR EACH STATEMENT EXECUTE FUNCTION func_ensure_default_exists();

方案优势 #

• 无额外表冗余，所有逻辑都基于现有分类表实现
• 所有约束都在数据库层落地，不需要业务代码做兼容判断
• 性能损耗极低，部分唯一索引仅存储默认行的索引数据，触发器逻辑都是轻量操作

内容的提问来源于stack exchange，提问作者James4645