如何约束Oracle jobs表，避免不同ID记录重复使用相同code与name

根据你的需求——同ID可插入多条历史记录（允许code/name变化），但新ID的记录不能复用其他ID已存在的code+name组合，以下是两种可行的实现方案：

方案一：行级触发器（推荐，逻辑清晰且易维护） #

通过触发器在插入前判断：如果是全新ID的记录，则校验code+name组合未被其他ID占用，同时加入锁机制避免并发插入冲突。

触发器代码 #

create or replace trigger trg_jobs_unique_code_name_across_ids
before insert on jobs
for each row
declare
    v_existing_id_count number;
    v_duplicate_count number;
begin
    -- 检查当前ID是否已存在（判断是否为同一job的历史版本）
    select count(*)
    into v_existing_id_count
    from jobs
    where id = :new.id;

    if v_existing_id_count = 0 then
        -- 新ID场景：检查code+name是否已被其他ID使用，同时锁定匹配记录防止并发冲突
        select count(*)
        into v_duplicate_count
        from jobs
        where code = :new.code
          and name = :new.name
          for update;

        if v_duplicate_count > 0 then
            raise_application_error(
                -20001,
                '错误：code与name的组合已被其他ID的记录占用，无法创建新ID记录'
            );
        end if;
    end if;
end;
/

额外补充：强制历史记录插入规则 #

为了确保用户只能通过插入新记录来更新code/name（而不是直接修改现有记录），可以添加一个禁止更新核心字段的触发器：

create or replace trigger trg_jobs_prevent_update
before update on jobs
for each row
begin
    -- 禁止修改code、name、id字段，强制插入新记录保存历史版本
    if :old.code != :new.code or :old.name != :new.name or :old.id != :new.id then
        raise_application_error(-20002, '错误：禁止修改code、name或ID字段，请插入新记录以保存历史版本');
    end if;
end;
/

方案二：物化视图+唯一约束（适合严格要求code+name仅归属一个ID的场景） #

如果你的需求是任何code+name组合只能被一个ID使用（同一ID的多条记录可重复该组合），可以通过物化视图存储每个ID的初始code+name，再添加唯一约束：

步骤1：创建物化视图 #

create materialized view mv_jobs_unique_code_name
refresh fast on commit
as
select id, code, name
from jobs
where rowid in (
    select min(rowid)
    from jobs
    group by id
);

步骤2：添加唯一约束 #

alter table mv_jobs_unique_code_name add constraint uq_mv_jobs_code_name unique (code, name);

该方案通过物化视图维护每个ID的初始code+name，确保同一组合不会被分配给多个ID，但会增加数据维护成本，需根据实际场景选择。

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