Quem mexeu na minha PK?

Mexer numa Chave Primária é sempre algo trabalhoso. Se o sistema for grande e que já estiver em produção então… é no mínimo delicado. Há uma série de pequenos passos propensos a erros que podem acontecer no caminho.

Tive estes dias de alterar o conteúdo de uma PK vou mostrar aqui o que eu fiz para evitar alguns erros comuns e agilizar a operação.

A idéia aqui é utilizar um pouco de SQL para gerar a maior parte dos comandos DDL para nós sem ter que digitar linha por linha, consultando quase sempre o nosso dicionário de dados.

Neste exemplo, foi alterado o conteúdo de uma PK, como por exemplo trocar o número do RG pelo CPF. A estrutura das operações é algo assim:

1. Tirar backup de tudo antes de mais nada!
2. Criar uma tabela ‘temp’ para conter os dados relativos às tabelas e campos que serão afetados;
3. Carretar a tabela com os dados;
4. Remover todas as chaves estrangeiras das tabelas citadas em ‘temp’;
5. Remover todas as chaves primárias das tabelas citatas em ‘temp’;
6. Tirar um backup dos dados da coluna atual renomeando colunas que serão alteradas com o sufixo ‘_old’;
7. Checar e remover as restrições de ‘NOT NULL’ nas colunas renomeadas;
8. Criar novas colunas para receber os dados;
9. Rodar scripts para atualizar os dados nos novos campos;
10. Recriar as chaves primárias removidas;
11. Recriar as chaves estrangeiras removidas;
12. Testar

Ao invés de rodar cada etapa isoladamente a minha idéia é primeiro rodar os scripts abaixo seqüencialmente, guardar o resultado de cada um e só depois de gravar em disco todos o resultado guardado é que você começa a executá-los. O motivo disto é bastante simples: os scripts irão remover uma série de constraints criando comandos do tipo ALTER TABLE DROP. Se você rodar estes comandos antes de gerar os comandos que irão recria-los no final do processo, você não conseguirá descobrir como eram os constraints, portanto não poderá recriar os comandos ALTER TABLE ADD. Se fizer isso, você terá que recorrer a sua documentação e criar os comandos na mão.

Não deixe de fazer um backup disto e nunca execute isto pela primeira vez direto na base de produção. Utilize uma base de teste primeiro.

Bom… vamos colocar a mão na massa agora e ver como podemos agilizar algumas coizas:

• Criar a tabela ‘temp’

--DROP TABLE temp;
CREATE TABLE tabelas (
  nome_tabela    varchar2(200),
  campo          varchar2(200),
CONSTRAINT temp_pk PRIMARY KEY (nome_tabela, campo)
);

• Popular a tabela

Coloque na forma de inserts (um bom editor e um pouco de regex vão lhe ajudar) o nome das tabelas e colunas que sofrerão alteração.

INSERT INTO temp VALUES ('tabela_a', 'campo_a');
INSERT INTO temp VALUES ('tabela_b', 'campo_b');
INSERT INTO temp VALUES ('tabela_c', 'campo_c');...
...
...
COMMIT;

Fazer isso parece dar mais trabalho, mas na verdade reaproveitaremos estes valores algumas vezes, acaba sendo bastante prático.

• Remover os constraints FK

SELECT DISTINCT
  'ALTER TABLE '
    || table_name
    || ' DROP CONSTRAINT '
    || constraint_name
    || ' CASCADE;' sql
  FROM all_constraints
  WHERE
    constraint_type='R' AND
    (
      table_name IN ( SELECT nome_tabela FROM temp ) OR
      r_constraint_name IN (
        SELECT constraint_name
          FROM user_constraints
          WHERE table_name IN (
            SELECT nome_tabela FROM temp
          )
        )
      )
  ORDER BY sql;

• Remover os constraints PK:

SELECT 'ALTER TABLE '
  || table_name
  || ' DROP CONSTRAINT '
  || constraint_name
  || ' CASCADE;' sql
  FROM all_constraints
  WHERE
    constraint_type='P' AND
    table_name IN (SELECT nome_tabela FROM temp )
  ORDER BY sql
;

• Renomear os campos:

SELECT
  'ALTER TABLE '
    || nome_tabela
    || ' RENAME COLUMN '
    || campo
    || ' TO '
    || campo
    || '_OLD;' sql
  FROM temp;

• Alterar para NULL os campos renomeados

SELECT
  'ALTER TABLE '
    || nome_tabela
    || ' MODIFY('
    || campo
    || '_OLD NULL);' sql
  FROM temp;

• Recriar os campos para receberem os novos valores:

  SELECT 'ALTER TABLE '
    || b.nome_tabela
    || ' ADD ('
    || b.campo
    || ' '
    || a.data_type
    || ' '
    || DECODE (a.data_type,
      'NUMBER', '(' || a.data_length || ',' || a.data_precision || ') ',
      'VARCHAR2', '(' || a.data_length || ') ',
      'CHAR', '(' || a.data_length || ') ',
      'RAW', '(' || a.data_length || ') ')
    || DECODE (a.nullable, 'NULL', 'NOT NULL ') || ');'
  FROM
    user_tab_columns a,
    temp b
  WHERE
    a.table_name = b.nome_tabela AND
    a.column_name = b.campo
  ORDER BY b.nome_tabela, b.campo
;

Aqui houve uma aplicação da “lei no mínimo esforço” no sentido em que me preocupei apenas com os tipos de dados que eu já sabia que seriam afetatos, portanto não me preocupei com outras possibilidades. Isto poderia ser facilmente ser resolvido ampliando este SQL.

• Recriar as PKs:

SET serveroutput ON;
BEGIN
  dbms_output.put_line('-- Criando PKs');
END;

DECLARE
  CURSOR c_constraints IS
    SELECT constraint_name, table_name
      FROM all_constraints
      WHERE
        constraint_type = 'P' AND
        table_name IN (SELECT DISTINCT nome_tabela FROM temp)
      ORDER BY table_name;
  CURSOR c_columns (p_constraint_name varchar2) IS
    SELECT column_name
      FROM all_cons_columns
      WHERE constraint_name = p_constraint_name
      ORDER BY column_name;
BEGIN
  FOR r_constraints IN c_constraints LOOP
    dbms_output.put_line('ALTER TABLE ' || r_constraints.table_name || ' ADD CONSTRAINT ' ||                    r_constraints.constraint_name || ' PRIMARY KEY(');
    dbms_output.put('    ');
    FOR r_columns IN c_columns(r_constraints.constraint_name) LOOP
      dbms_output.put(r_columns.column_name || ', ');
    END LOOP;
    dbms_output.put_line(');');
  END LOOP;
END;

• Recriar FKs

SET serveroutput ON;
BEGIN
  dbms_output.put_line('-- Criando FKs');
END;

DECLARE
  CURSOR c_constraints IS
    SELECT
      a.constraint_name,
      a.table_name table_from,
      a.r_constraint_name,
      b.table_name table_to
    FROM
      all_constraints a,
      all_constraints b
    WHERE
      a.constraint_type = 'R' AND
      a.r_constraint_name = b.constraint_name AND
      (
        a.table_name IN ( SELECT nome_tabela FROM temp ) OR
        a.constraint_name IN (
          SELECT constraint_name FROM user_constraints WHERE table_name IN (
            SELECT nome_tabela FROM temp
          )
        )
      )
    ORDER BY a.table_name;
  CURSOR c_columns (p_constraint_name varchar2) IS
    SELECT column_name
      FROM user_cons_columns
      WHERE constraint_name = p_constraint_name
      ORDER BY column_name;
BEGIN
  FOR r_constraints IN c_constraints LOOP
    dbms_output.put('ALTER TABLE '
      || r_constraints.table_from
      || ' ADD CONSTRAINT '
      ||   r_constraints.constraint_name
      || ' FOREIGN KEY(');
--        dbms_output.put('    ');
    FOR r_columns IN c_columns(r_constraints.constraint_name) LOOP
      dbms_output.put(r_columns.column_name || ', ');
    END LOOP;
    dbms_output.put_line(')');
    dbms_output.put('    REFERENCES ' || r_constraints.table_to || ' (');
--        dbms_output.put('        ');
    FOR r_columns IN c_columns(r_constraints.r_constraint_name) LOOP
      dbms_output.put(r_columns.column_name || ', ');
    END LOOP;
    dbms_output.put_line(');');
  END LOOP;
END;

OBS:Não esqueça de verificar se o número de Constraints removidas é igual ao número de constraints criadas.

Os scripts para criar as constraints deram um pouco mais de trabalho e podem quebrar um bom galho em outras situações com poucas adaptações, como quando você precisa adicionar ou remover um campo numa PK composta.

Estes Scripts foram testados no Oracle 9i. Sei que seria simples fazer algo semelhante para o PostgreSQL ou qualquer outro SGDB com acesso ao ‘Information Schema’ (o Oracle não usa este nome pois não segue o padrão SQL neste ponto) e alguma linguagem PL.