PostgreSQL 已有唯一约束却出现重复默认配置：NULL 语义的定位与修复

适用场景与现象

配置表按租户和作用域保存覆盖项：scope_id 有值表示某个具体作用域，为 NULL 表示租户默认配置。业务要求每个租户只能有一条默认配置，每个具体作用域也只能有一条。

表上已经有 UNIQUE (tenant_id, scope_id)，压测后却出现两条默认配置。读取接口用 LIMIT 1 暂时掩盖了问题，修改配置时又只更新其中一条，最终表现为配置偶尔“回退”。

先别把原因归到连接池或读写延迟。检查重复记录的业务键，若重复只发生在 scope_id IS NULL，应先核对唯一约束对空值的处理。

本文使用普通、非分区表；SQL 示例面向 PostgreSQL 14 及以上，单独标注的 NULLS NOT DISTINCT 方案要求 15 及以上。示例数据均为构造数据。

1. 在临时表中复现，不碰业务表

在同一个 PostgreSQL 会话执行：

CREATE TEMP TABLE config_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    scope_id bigint,
    config_value text NOT NULL,
    CONSTRAINT config_demo_tenant_scope_key UNIQUE (tenant_id, scope_id)
);

INSERT INTO config_demo (tenant_id, scope_id, config_value)
VALUES (7, NULL, '默认配置甲'), (7, NULL, '默认配置乙');

SELECT tenant_id, scope_id, count(*) AS duplicate_count
FROM config_demo
GROUP BY tenant_id, scope_id
HAVING count(*) > 1;

预期返回租户 7、空作用域、数量 2。两次插入都能成功，约束并没有失效：PostgreSQL 默认的唯一约束允许包含空值的重复键。业务把空值解释成同一个“默认作用域”，数据库默认规则没有表达这层含义。官方唯一约束说明

这里也能看到一个容易误判的细节：GROUP BY 能把空作用域归为一组，并不意味着默认唯一约束也按同样规则判重。

实际排查时，将上面的聚合查询用于业务表，并确认真正的约束定义：

SELECT conname, pg_get_constraintdef(oid) AS constraint_definition
FROM pg_constraint
WHERE conrelid = 'config_demo'::regclass
  AND contype IN ('p', 'u');

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname LIKE 'pg_temp_%'
  AND tablename = 'config_demo';

业务表应使用带 schema 的表名，并把索引查询的 schema、表名条件换成真实值。不要只看 ORM 模型声明，要确认迁移实际落到了数据库。

2. 修复方案：保留原约束，补上默认配置的唯一性

本例只有 scope_id 可空，可以保留原有复合唯一约束，再增加一个仅覆盖默认配置的唯一索引。下面先清空临时复现数据，业务迁移不能照抄这条清空命令。

TRUNCATE config_demo;

CREATE UNIQUE INDEX config_demo_default_key
ON config_demo (tenant_id)
WHERE scope_id IS NULL;

INSERT INTO config_demo (tenant_id, scope_id, config_value)
VALUES (7, NULL, '默认配置甲');

INSERT INTO config_demo (tenant_id, scope_id, config_value)
VALUES (7, NULL, '默认配置乙')
ON CONFLICT (tenant_id) WHERE scope_id IS NULL
DO UPDATE SET config_value = EXCLUDED.config_value;

SELECT tenant_id, scope_id, config_value FROM config_demo;

预期只有一行，内容更新为“默认配置乙”。新索引中的键是非空的 tenant_id，空作用域只作为筛选条件。因此同一租户的默认配置会竞争同一个唯一键。部分唯一索引说明

两层规则各有职责：

• 原复合唯一约束限制有值的 (tenant_id, scope_id)。
• 新部分唯一索引限制每个租户最多一条默认配置。
• 不同租户仍可以各自保存默认配置。

不要把新索引写成 ON config_demo (tenant_id, scope_id) WHERE scope_id IS NULL：索引键里仍有空值，默认空值判重问题没有消失。

示例的冲突更新表示“后一次写入覆盖前一次”，不是所有配置系统都适用。若要求拒绝覆盖，使用普通 INSERT，将唯一冲突转换成明确的业务错误；若要求乐观锁，另加版本条件。应用 SQL 必须绑定参数，不能拼接租户、作用域和配置内容。

3. PostgreSQL 15 以上的另一种表达

如果业务要求这个组合键里的空值也参与判重，可以直接声明：

CREATE TEMP TABLE config_strict_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    scope_id bigint,
    config_value text NOT NULL,
    CONSTRAINT config_strict_tenant_scope_key
        UNIQUE NULLS NOT DISTINCT (tenant_id, scope_id)
);

INSERT INTO config_strict_demo (tenant_id, scope_id, config_value)
VALUES (7, NULL, '默认配置甲');

INSERT INTO config_strict_demo (tenant_id, scope_id, config_value)
VALUES (7, NULL, '默认配置乙')
ON CONFLICT ON CONSTRAINT config_strict_tenant_scope_key
DO UPDATE SET config_value = EXCLUDED.config_value;

这是替代方案，不需要在同一张表同时部署两套修复。该能力在 PostgreSQL 15 加入，升级前应先核对服务器版本。PostgreSQL 15 发布说明

如果多个键列都允许为空，需要逐一明确组合的业务含义；不能把本例“一列可空”的部分索引写法机械推广到所有空值组合。把 NULL 替换成 0 或空字符串也不是通用修复，会引入真实值与占位值碰撞的问题。

4. 生产迁移先处理重复，再建立约束

建议按下面的顺序安排变更：

1. 统计重复键，导出涉及的记录 ID，确认配置内容是否冲突以及是否被其他表引用。
2. 按业务规则合并或选定保留记录，调整引用并保留可恢复的变更记录。不要未经确认就按最小 ID 批量删除。
3. 暂停相关写入口，覆盖后台任务、管理入口和旧版本服务；清理后再次查询，确认没有重复。
4. 建立索引，执行正反例验证，随后恢复写入。若不能暂停写入，需单独设计并发写保护和失败重试，不能把“清理完成”当作后续不会再有重复的保证。

小表维护窗口可使用普通 CREATE UNIQUE INDEX，但要评估其阻塞写入的时间。大表可以评估 CREATE UNIQUE INDEX CONCURRENTLY；它不能放在事务块中，且失败后需要检查是否留下无效索引。CREATE INDEX 官方说明

本例没有提供自动清理生产数据的脚本，因为“哪条配置应该保留”需要业务决定。回滚索引只能撤回约束，无法恢复已合并的数据；数据回滚必须依赖合并前保存的记录。

如果采用 15 以上的新约束替换旧约束，还要检查原约束是否被外键等对象依赖，以及旧服务的冲突处理是否兼容。不要在所有写入口完成适配前直接删除旧约束。

5. 用离线测试覆盖规则，数据库集成测试另行完成

下面用 Python 标准库 SQLite 在内存中验证“旧复合唯一约束 + 新部分唯一索引”的业务规则。保存为 test_config_unique.py，执行 python -m unittest -v test_config_unique.py，不需要额外依赖。

SQLite 与 PostgreSQL 是不同引擎。本测试只覆盖本例整数键和部分唯一索引的判重行为，不验证 PostgreSQL 的 DDL、并发等待、SQLSTATE 或 NULLS NOT DISTINCT。

import sqlite3
import unittest


class ConfigUniqueTests(unittest.TestCase):
    """验证默认配置与具体作用域配置的唯一性边界。"""

    def setUp(self) -> None:
        self.connection = sqlite3.connect(":memory:")
        self.addCleanup(self.connection.close)
        self.connection.execute(
            "CREATE TABLE config (tenant_id INTEGER NOT NULL, "
            "scope_id INTEGER, config_value TEXT NOT NULL, "
            "UNIQUE (tenant_id, scope_id))"
        )

    def add_default_index(self) -> None:
        """让空作用域按非空租户键判重。"""
        self.connection.execute(
            "CREATE UNIQUE INDEX config_default_key ON config (tenant_id) "
            "WHERE scope_id IS NULL"
        )

    def insert_config(self, tenant_id: int, scope_id: int | None) -> None:
        """通过绑定参数构造测试配置。"""
        self.connection.execute(
            "INSERT INTO config VALUES (?, ?, ?)",
            (tenant_id, scope_id, "示例配置"),
        )

    def test_old_constraint_allows_duplicate_defaults(self) -> None:
        self.insert_config(7, None)
        self.insert_config(7, None)
        count = self.connection.execute("SELECT count(*) FROM config").fetchone()[0]
        self.assertEqual(count, 2)

    def test_duplicate_default_is_rejected(self) -> None:
        self.add_default_index()
        self.insert_config(7, None)
        with self.assertRaises(sqlite3.IntegrityError):
            self.insert_config(7, None)

    def test_other_tenant_default_is_allowed(self) -> None:
        self.add_default_index()
        self.insert_config(7, None)
        self.insert_config(8, None)

    def test_distinct_scopes_and_default_can_coexist(self) -> None:
        self.add_default_index()
        for scope_id in (None, 10, 11):
            self.insert_config(7, scope_id)

    def test_duplicate_named_scope_is_rejected(self) -> None:
        self.add_default_index()
        self.insert_config(7, 10)
        with self.assertRaises(sqlite3.IntegrityError):
            self.insert_config(7, 10)

    def test_named_scope_in_other_tenant_is_allowed(self) -> None:
        self.add_default_index()
        self.insert_config(7, 10)
        self.insert_config(8, 10)

    def test_update_to_duplicate_default_is_rejected(self) -> None:
        self.add_default_index()
        self.insert_config(7, None)
        self.insert_config(7, 10)
        with self.assertRaises(sqlite3.IntegrityError):
            self.connection.execute(
                "UPDATE config SET scope_id = NULL "
                "WHERE tenant_id = ? AND scope_id = ?", (7, 10)
            )

    def test_index_creation_detects_existing_duplicates(self) -> None:
        self.insert_config(7, None)
        self.insert_config(7, None)
        with self.assertRaises(sqlite3.IntegrityError):
            self.add_default_index()

接入 PostgreSQL 的测试环境后，还要执行：

• 同一租户插入两条默认配置，第二条普通插入应报唯一冲突，SQLSTATE 为 23505；回滚失败事务后继续其他验证。
• 两个独立连接同时写同一默认配置，先提交一个事务，再确认另一个按照普通插入或冲突更新策略处理；为测试设置超时，避免一直等待。
• 将已有具体作用域改为空作用域，确认不会绕过限制。
• 对冲突更新验证最终内容和行数，并验证应用没有吞掉异常后仍返回成功。
• 用真实迁移账号检查 DDL 权限、锁等待和迁移工具事务模式；用应用账号检查租户权限条件。

本次本地环境未连接 PostgreSQL，以上集成检查及迁移耗时需要在接入环境完成，不能由 SQLite 测试推断通过。

6. 防止再次出现

数据库模型评审时，把“空值表示未知”与“空值表示唯一的默认项”区分清楚。后者需要数据库层明确约束，不能仅靠应用先查后插：两个请求可能同时查不到记录，然后同时插入。

应用应识别唯一冲突并选择拒绝、覆盖或版本冲突策略；日志记录约束名、操作类型、错误码和脱敏后的业务标识，不记录配置原文。上线后保留低频重复键巡检，结果必须为零。

这类故障的修复点在业务键定义：让数据库实际执行的判重规则，与业务对“默认配置只有一条”的要求一致。唯一约束的存在本身，并不代表它已经表达了全部业务规则。