Oracle数据库中修改表上CHECK约束的最佳方法是什么？

Oracle本身不支持直接修改已有CHECK约束的校验规则，以下方案可实现无业务中断的约束更新，全程无校验空窗，不会影响数据库正常运行：

操作步骤 #

• 1. 查询现有CHECK约束的基础信息
     执行如下SQL获取旧约束的名称、校验规则等信息：

  SELECT constraint_name, search_condition, status 
  FROM user_constraints 
  WHERE table_name = 'YOUR_TABLE_NAME' 
    AND constraint_type = 'C';
  

  注意替换SQL中的表名为实际表名，Oracle系统表中存储的表名、约束名均为大写
• 2. 新增符合要求的新CHECK约束
     使用ENABLE NOVALIDATE参数创建约束，仅校验新增/更新的数据，不回溯校验历史存量数据，执行速度极快，仅持有毫秒级排他锁，不会阻塞业务DML：

  ALTER TABLE YOUR_TABLE_NAME 
  ADD CONSTRAINT NEW_CHECK_CONSTRAINT_NAME CHECK (新的校验规则) 
  ENABLE NOVALIDATE;
  

  执行完成后新约束即时生效，此时新旧约束同时生效，不会出现校验空窗，避免非法数据入库。
• 3. （可选）校验存量历史数据
     如果需要新约束对历史数据也生效，可在业务低峰期执行校验操作，Oracle 12c及以上版本可加ONLINE参数避免长时间锁表：

  -- 12c及以上版本支持ONLINE参数，不阻塞业务DML
  ALTER TABLE YOUR_TABLE_NAME 
  MODIFY CONSTRAINT NEW_CHECK_CONSTRAINT_NAME VALIDATE ONLINE;
  
  -- 12c以下版本执行，会短暂持有排他锁，建议低峰期执行
  -- ALTER TABLE YOUR_TABLE_NAME MODIFY CONSTRAINT NEW_CHECK_CONSTRAINT_NAME VALIDATE;
  

  如果存量数据存在不符合新约束的记录，需要先清理异常数据后再执行校验操作
• 4. 删除旧的CHECK约束
     确认新约束运行正常后，删除旧约束即可完成更新：

  ALTER TABLE YOUR_TABLE_NAME DROP CONSTRAINT OLD_CHECK_CONSTRAINT_NAME;
  

回滚方案 #

如果新约束不符合业务要求，在删除旧约束前可直接回滚，无业务影响：

ALTER TABLE YOUR_TABLE_NAME DROP CONSTRAINT NEW_CHECK_CONSTRAINT_NAME;

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