Oracle中如何高效批量将所有PK、FK修改为DEFERRABLE约束

Oracle 全版本（含12c/19c/21c）均不提供直接修改已有约束DEFERRABLE/NOT DEFERRABLE属性的官方语法，所有跳过重建直接修改属性的方案本质都是违规修改底层数据字典，会引发数据字典不一致、数据库故障等严重问题，绝对不要在生产环境使用。
属性调整本质确实需要重建约束，但完全不需要手动梳理约束依赖、逐行手写删除/重建语句，通过系统内置的数据字典视图可以一键生成全量、顺序正确的执行脚本，全程不需要人工干预依赖排序，50张表规模的库整个操作耗时不超过10分钟，出错概率极低。

所有脚本均通过Oracle内置数据字典生成，自动处理联合主键、联合外键等场景，不需要人工梳理表和列的对应关系。

第一步：生成全量外键删除脚本 #

外键依赖主键/唯一约束，必须先删除所有外键才能修改被依赖的主键属性，执行以下SQL生成删除脚本：

-- 生成所有需要调整的外键删除语句
spool drop_fk.sql
SELECT 'ALTER TABLE "' || c.table_name || '" DROP CONSTRAINT "' || c.constraint_name || '";' AS ddl_stmt
FROM user_constraints c
WHERE c.constraint_type = 'R' -- R类型对应外键约束
  AND c.deferrable = 'NOT DEFERRABLE'; -- 仅筛选需要调整属性的约束
spool off

第二步：生成主键/唯一约束的删除+重建脚本 #

删完外键后即可处理主键、唯一约束，重建时直接带上DEFERRABLE INITIALLY IMMEDIATE属性，以下SQL自动处理多列联合约束场景：

-- 生成主键/唯一约束的删除、重建语句
spool recreate_pk_uk.sql
WITH cons_cols AS (
  SELECT 
    c.table_name,
    c.constraint_name,
    c.constraint_type,
    LISTAGG('"' || cc.column_name || '"', ', ') WITHIN GROUP (ORDER BY cc.position) AS col_list
  FROM user_constraints c
  JOIN user_cons_columns cc 
    ON c.constraint_name = cc.constraint_name 
    AND c.owner = cc.owner
  WHERE c.constraint_type IN ('P', 'U') -- P对应主键，U对应唯一约束
    AND c.deferrable = 'NOT DEFERRABLE'
  GROUP BY c.table_name, c.constraint_name, c.constraint_type
)
SELECT 
  'ALTER TABLE "' || table_name || '" DROP CONSTRAINT "' || constraint_name || '";' || CHR(10) ||
  'ALTER TABLE "' || table_name || '" ADD CONSTRAINT "' || constraint_name || '" ' ||
  CASE constraint_type WHEN 'P' THEN 'PRIMARY KEY' ELSE 'UNIQUE' END ||
  ' (' || col_list || ') DEFERRABLE INITIALLY IMMEDIATE;' AS ddl_stmt
FROM cons_cols;
spool off

第三步：生成外键重建脚本 #

主键/唯一约束重建完成后，执行以下SQL生成外键重建脚本，重建时同样设置目标属性：

-- 生成所有外键重建语句
spool recreate_fk.sql
WITH fk_cols AS (
  SELECT
    c.table_name AS child_table,
    c.constraint_name AS fk_name,
    c.r_constraint_name AS ref_pk_name,
    LISTAGG('"' || cc.column_name || '"', ', ') WITHIN GROUP (ORDER BY cc.position) AS child_col_list,
    MAX(rc.table_name) AS parent_table
  FROM user_constraints c
  JOIN user_cons_columns cc
    ON c.constraint_name = cc.constraint_name
    AND c.owner = cc.owner
  JOIN user_constraints rc
    ON c.r_constraint_name = rc.constraint_name
    AND c.r_owner = rc.owner
  WHERE c.constraint_type = 'R'
    AND c.deferrable = 'NOT DEFERRABLE'
  GROUP BY c.table_name, c.constraint_name, c.r_constraint_name
),
ref_pk_cols AS (
  SELECT
    rc.constraint_name,
    LISTAGG('"' || rcc.column_name || '"', ', ') WITHIN GROUP (ORDER BY rcc.position) AS parent_col_list
  FROM user_constraints rc
  JOIN user_cons_columns rcc
    ON rc.constraint_name = rcc.constraint_name
    AND rc.owner = rcc.owner
  WHERE rc.constraint_type IN ('P','U')
  GROUP BY rc.constraint_name
)
SELECT
  'ALTER TABLE "' || fc.child_table || '" ADD CONSTRAINT "' || fc.fk_name || '" FOREIGN KEY (' || fc.child_col_list || ') ' ||
  'REFERENCES "' || fc.parent_table || '" (' || rpc.parent_col_list || ') DEFERRABLE INITIALLY IMMEDIATE;' AS ddl_stmt
FROM fk_cols fc
JOIN ref_pk_cols rpc ON fc.ref_pk_name = rpc.constraint_name;
spool off

• 执行脚本前先在测试环境完整跑一遍，确认生成的DDL符合预期，同时验证存量数据不会触发约束冲突
• 正式执行时建议暂停业务写入，避免操作过程中新数据写入导致约束创建失败
• 生成的所有DDL脚本提前留存备份，执行过程中如果出现问题可以快速回退
• 如果需要调整的约束归属于其他schema，将上述SQL中的user_constraints、user_cons_columns替换为all_constraints、all_cons_columns，增加owner字段筛选即可
• 数据字典返回的约束关联关系完全准确，不需要人工额外核对依赖顺序，不会出现先删主键、后删外键这类顺序错误

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