如何为主键和jsonb类型字段维护多版本分支并支持变更审批

1. 调整表结构，替换单主键为业务键+状态约束 #

保留JSONB字段选型不变，取消registration_NO的单主键约束，调整为以下表结构（以PostgreSQL为例）：

• id：自增主键，全局唯一标识
• registration_NO：工商注册号，业务唯一标识，不设为主键
• address：地址字段，可按需设置为普通字符串或JSONB类型
• contact_info：JSONB类型，存储手机号数组
• directors：JSONB类型，存储董事数组
• version_status：枚举类型，可选值为approved（已生效）、pending（待审批）、rejected（已驳回）、deprecated（已过期）
• create_time：版本创建时间
• approve_time：审批通过时间，已生效版本必填

核心约束配置：通过部分唯一索引限制同一个registration_NO下仅允许存在一条已生效记录，从底层避免多正式版本冲突：

CREATE UNIQUE INDEX idx_regno_approved ON business_entity (registration_NO) 
WHERE version_status = 'approved';

该约束不会限制待审批、已驳回等其他状态的记录插入，完美解决主键重复报错问题。

2. 核心操作流程 #

• 提交变更申请：直接插入一条version_status = 'pending'的新记录，JSONB字段存储修改后的完整值即可，不会覆盖原有已生效记录，也不会触发唯一键冲突。需要新增/删除董事、手机号时，可直接调用PostgreSQL原生jsonb_insert、jsonb_delete等函数处理已生效版本的JSONB值，无需手动拼接内容。
• 审批通过操作：整个流程包裹在事务中保证原子性：首先将原有approved状态的记录更新为deprecated，再将待审批记录的状态改为approved，最后填充approve_time字段即可。
• 审批驳回操作：直接将待审批记录的状态更新为rejected，不影响正式版本对外展示。

3. 对外查询实现 #

常规业务查询仅需要加状态过滤条件，即可保证只返回已生效的正式版本：

SELECT registration_NO, address, contact_info, directors 
FROM business_entity 
WHERE registration_NO = '100' AND version_status = 'approved';

需要查询待审批版本时，调整过滤条件即可：

SELECT * FROM business_entity 
WHERE registration_NO = '100' AND version_status = 'pending';

4. 可选优化 #

可新增is_latest_approved布尔字段，配合数据库触发器自动维护字段值，查询正式版本时直接过滤is_latest_approved = true即可，进一步提升查询性能。

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