Removendo chek constraints duplicadas em bases Oracle

Talvez você também esteja com problemas devido a check constraints do tipo "<coluna> not null" estarem duplicadas no seu banco.

No meu caso específico eu descobri que este problema ocorreu pois o time de "análise" utiliza uma ferramenta de UML (Enterprise Architect) para modelar um banco relacional quando deveria estar utilizando uma ferramenta de ER. A ferramenta definiu as colunas como não nulas, mas também criou check constraints para a forçar a restrição. Acontece que o Oracle (ao menos até a versão 10.2) não valida se constraints do tipo "check" possuem as mesmas clausulas de restrição.

Enfim vamos ao que interessa...

Para solucionar o problema escrevi o código de uma procedure que efetua a remoção das 2, ou mais, constraints do tipo "not null" na mesma coluna e por fim altera a tabela para redefinir a coluna, esta redefinição precisa ser feita pois com a remoção das contraints a coluna passaria a ser "nullable", por isto não basta só remover as constraints.

Antes da procedure de remoção das duplicações em si, é necessário também criar a procedure abaixo, para converter a coluna "search_condition" da view "all_constraints" de LONG para VARCHAR2, para que seja possível efetuar as comparações dos strings.

CREATE OR REPLACE FUNCTION  f_get_cons_check( p_owner VARCHAR2,p_constraint VARCHAR2)
    RETURN VARCHAR2
AS
   l_cursor    INTEGER DEFAULT dbms_sql.open_cursor;
   l_n         NUMBER;
   l_long_val  VARCHAR2(4000);
   l_long_len  NUMBER;
   l_buflen    NUMBER := 4000;
   l_curpos    NUMBER := 0;

BEGIN
   dbms_sql.parse( l_cursor,
        'SELECT search_condition 
           FROM all_constraints
          WHERE owner = :x
            AND constraint_name = :y',
        dbms_sql.native );
   dbms_sql.bind_variable( l_cursor, ':x', p_owner );
   dbms_sql.bind_variable( l_cursor, ':y', p_constraint );
   dbms_sql.define_column_long(l_cursor, 1);
   l_n := dbms_sql.execute(l_cursor);
   IF (dbms_sql.fetch_rows(l_cursor)>0)
   THEN
      dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos , l_long_val, l_long_len );
   END IF;
   dbms_sql.close_cursor(l_cursor);
   RETURN l_long_val;
END f_get_cons_check;

Esta função é baseada no código do Thomas Kyte publicada na issue "Long to Varchar2 conversion" do "Ask Tom"


E então temos a procedure para remoção das constraints duplicadas. Observe que estou apenas imprimindo os comandos de DML para remoção das constraints e alteração da tabela, mas nada impede que se você se sentir seguro altere estas chamadas para um "execute immediate".

PROCEDURE p_remove_dup_notnull_cons(p_schema VARCHAR2)
AS
  TYPE dup_constraints_type  IS RECORD
    ( owner            all_constraints.owner%TYPE,
      table_name       all_constraints.table_name%TYPE,
      column_name      all_cons_columns.column_name%TYPE,
      constraint_name  all_constraints.constraint_name%TYPE,
      cnt_check        NUMBER,
      ord              NUMBER);
      
  TYPE ref_cursor IS REF CURSOR RETURN dup_constraints_type;
  
  c_dup_constraints ref_cursor;
  
  TYPE t_dup_constraints IS TABLE OF dup_constraints_type
      INDEX BY PLS_INTEGER;
  
  l_dup_constraints t_dup_constraints;
  
  l_row             PLS_INTEGER;
  v_cnt             INTEGER;
  v_ddl_modify_coln VARCHAR2(4000);
  
  
BEGIN

  OPEN  c_dup_constraints FOR 
        SELECT owner, table_name, column_name, constraint_name, cnt_check, ord
          FROM( SELECT owner, table_name, column_name, constraint_name, 
                       count(*) over (partition by owner, table_name, column_name) as cnt_check,
                       row_number() over (partition by owner, table_name, column_name order by last_change) as ord
                  FROM (SELECT owner, table_name, constraint_name, last_change, trim('"' FROM substr(cons_check,1,instr(cons_check,' ')-1)) as column_name
                          FROM (SELECT owner, table_name, constraint_name, last_change, f_get_cons_check( owner, constraint_name) as cons_check
                                  FROM all_constraints
                                 WHERE owner = p_schema
                                   AND constraint_type IN ('C','?'))
                          WHERE cons_check LIKE '%IS NOT NULL'))
              --WHERE cnt_check > 1
            ORDER BY table_name, column_name, ord;

  LOOP
    FETCH c_dup_constraints BULK COLLECT INTO l_dup_constraints LIMIT 1000;
    DBMS_OUTPUT.PUT_LINE('-- ' || l_dup_constraints.COUNT || ' DUPLICATED CONSTRAINTS FOUND');
    EXIT WHEN l_dup_constraints.COUNT = 0;
    l_row := l_dup_constraints.FIRST;
    WHILE l_row IS NOT NULL
    LOOP
       IF l_dup_constraints(l_row).ord = 1
       THEN
         -- GERA DDL PARA REDEFINIR A COLUNA COMO "NOT NULL" APÓS A REMOÇÃO DAS CONSTRAINTS
         v_ddl_modify_coln := f_ddl_mod_tab_column(l_dup_constraints(l_row).owner,
                                                   l_dup_constraints(l_row).table_name,
                                                   l_dup_constraints(l_row).column_name,
                                                   ' NOT NULL ENABLE');
       END IF;
       
       -- DROPA CONSTRAINT DUPLICADA
       DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || l_dup_constraints(l_row).owner || 
                          '.' || l_dup_constraints(l_row).table_name ||
                          ' DROP CONSTRAINT ' || l_dup_constraints(l_row).constraint_name || ';');
       
       IF l_dup_constraints(l_row).ord = l_dup_constraints(l_row).cnt_check
       THEN
         -- EXECUTA DDL MODIFY PARA REDEFINIR A COLUNA COMO "NOT NULL"
         DBMS_OUTPUT.PUT_LINE(v_ddl_modify_coln || ';');
       END IF;
       l_row := l_dup_constraints.NEXT(l_row);
    END LOOP;
  
  END LOOP;

  IF c_dup_constraints%ISOPEN
  THEN
    CLOSE c_dup_constraints;
  END IF;

EXCEPTION 
WHEN OTHERS THEN
    IF c_dup_constraints%ISOPEN
    THEN
      CLOSE c_dup_constraints;
    END IF;
    RAISE;
END p_remove_dup_notnull_cons;

Sinta-se livre para utilizar e modificar estas rotinas como bem entender, por sua conta e risco. Mas se for publica-la por favor referencie minha autoria.