Oracle中与SQL Server IDENTITY_INSERT开关等效的功能实现方法

你当前使用的是Oracle 12c及以上版本支持的GENERATED ALWAYS AS IDENTITY标识列，默认强制由系统自动生成列值，禁止手动指定，对应SQL Server的开关操作可以按以下步骤实现：

通用方案（兼容Oracle 12c及所有更高版本） #

1. 插入指定标识值前，临时修改标识列生成规则 #

执行以下语句放开手动插入限制：

ALTER TABLE ProductCategory MODIFY ProductSK GENERATED BY DEFAULT ON NULL AS IDENTITY;

2. 手动插入指定SK值的行 #

此时可以正常插入你需要的维度表未知成员行：

INSERT INTO ProductCategory (ProductSK, ProductID, ProductName, BI_StartDate, BI_EndDate)
VALUES (-1, -1, 'Undefined', 99991231, 99991231);
COMMIT;

3. 插入完成后恢复标识列默认规则 #

改回强制系统生成的模式，避免后续业务插入时手动指定值破坏维度SK的连续性：

ALTER TABLE ProductCategory MODIFY ProductSK GENERATED ALWAYS AS IDENTITY;

4. （可选）调整序列起始值避免冲突 #

如果你手动插入的SK值大于当前标识序列的最大生成值，需要执行以下语句自动将序列下一个生成值调整为当前表中最大SK+1，防止后续自动插入时报唯一约束冲突：

ALTER TABLE ProductCategory MODIFY ProductSK GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE);

Oracle 18c+ 简化方案 #

18c及以上版本支持会话级的标识列插入开关，无需修改表结构：

-- 开启允许手动插入标识值
ALTER SESSION SET "_IDENTITY_INSERT" = ON;

-- 执行插入操作
INSERT INTO ProductCategory (ProductSK, ProductID, ProductName, BI_StartDate, BI_EndDate)
VALUES (-1, -1, 'Undefined', 99991231, 99991231);
COMMIT;

-- 关闭开关恢复默认规则
ALTER SESSION SET "_IDENTITY_INSERT" = OFF;

注意事项 #

• 修改表结构的操作需要账号拥有对应表的ALTER权限
• 插入特殊行后务必恢复标识列的默认生成规则，避免后续ETL流程出现异常
• 维度表的-1未知成员建议在初始化表阶段就插入，避免后续生产环境修改表结构

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