组织单元数据存储设计：代码名称唯一性及复用限制技术问询

场景说明 #

需构建一套存储组织信息的系统，支持每年一次的组织重组（修改组织name和code），部分变更可设置未来生效日期并自动生效。需保留org_unit的id字段，该字段被其他多张表引用。当前Oracle表结构如下：

create table org_units (
    id                number generated by default as identity
  , code              varchar2(30) not null
  , name              varchar2(100) not null
  , type              varchar2(10) not null
  , location_id       number references locations (id)
  -- 生效时间区间
  , effective_start_date    date not null
  , effective_end_date      date
  -- 审计字段
  , created_on      date   not null
  , created_by      number not null
  , last_updated_on date   not null
  , last_updated_by number not null
  , last_session_id number not null
  , constraint org_units_pk primary key (id,effective_start_date)
);

技术问题解答 #

1. 约束同一org_unit下活跃状态记录的code/name不重复 #

活跃状态定义：当前时间处于生效区间内的记录（effective_start_date <= SYSDATE AND (effective_end_date IS NULL OR effective_end_date > SYSDATE)）。可通过函数型唯一索引实现约束：

针对code的活跃唯一约束

CREATE UNIQUE INDEX org_units_active_code_uix 
ON org_units (
    id,
    CASE 
        WHEN effective_start_date <= SYSDATE AND (effective_end_date IS NULL OR effective_end_date > SYSDATE) 
        THEN code 
        ELSE NULL 
    END
);

针对name的活跃唯一约束

CREATE UNIQUE INDEX org_units_active_name_uix 
ON org_units (
    id,
    CASE 
        WHEN effective_start_date <= SYSDATE AND (effective_end_date IS NULL OR effective_end_date > SYSDATE) 
        THEN name 
        ELSE NULL 
    END
);

原理：Oracle唯一索引会忽略NULL值，仅对活跃状态的记录校验code/name唯一性，同一id下的活跃记录若出现重复值，会触发约束冲突。

2. 禁止复用同一org_unit曾使用过的code/name #

要确保同一id下所有历史记录（无论是否活跃）的code和name均不重复，直接创建全局唯一约束即可：

针对code的全局唯一约束（同一id下）

ALTER TABLE org_units 
ADD CONSTRAINT org_units_id_code_unique UNIQUE (id, code);

针对name的全局唯一约束（同一id下）

ALTER TABLE org_units 
ADD CONSTRAINT org_units_id_name_unique UNIQUE (id, name);

说明：这两个约束强制同一id的所有记录中，code和name各自保持唯一，彻底避免历史值复用。若允许不同id的组织使用相同code/name，该约束适用；若要求全局所有组织code唯一，只需去掉id字段，创建UNIQUE (code)即可。

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