Caio Moritz Ronchi

Minha saga e dos meus colegas de projeto com o Oracle continua. Estamos tentando configurar um framework web (o Rails) para que ele interfira o mínimo possível na definição original das tabelas do nosso banco de dados. Ela é uma base Oracle com convenções próprias que, idealmente, não deveria ter seu esquema modificado de forma a agradar nenhum framework. Não deveria.

Mas eis que o Rails não suporta tabelas com chaves primárias compostas (e a extensão Composite Primary Keys, que resolveria o problema, atualmente não está funcionando). Para quem não tem muita experiência com banco de dados, vou ilustrar com um exemplo de uma tabela que possui uma chave primária composta de 2 colunas (e não 1, que seria o ideal para se trabalhar com o Rails).

Em uma instituição de ensino, por exemplo, poderíamos ter alunos e cursos (disciplinas) vinculados entre si por meio da seguinte tabela:

create table matricula (
    cd_estudante int REFERENCES estudante (cd_estudante),
    cd_curso int REFERENCES curso (cd_curso),
    nota number,
    CONSTRAINT matricula_pk PRIMARY KEY (cd_estudante, cd_curso)
);

Nossa chave primária é, portanto, composta. Para que o Rails consiga interagir com essa tabela é necessário que ela tenha 1 única chave primária (neste caso, uma chave artificial, que chamarei de cd_artificial). Os campos cd_estudante e cd_curso podem continuar formando uma dupla de campos únicos, com a adição de uma restrição de integridade. Nossa tabela “Rails friendly” deveria ter, portanto, a seguinte definição:

create table matricula (
    cd_artificial int,
    cd_estudante int REFERENCES estudante (cd_estudante),
    cd_curso int REFERENCES curso (cd_curso),
    nota number,
    CONSTRAINT matricula_pk UNIQUE (cd_estudante, cd_curso)
);

O problema do nosso projeto é que algumas das tabelas que precisam sofrer essa metamorfose já possuem milhões de registros (ou seja, definir uma nova tabela, migrar os dados da antiga para a nova e remover a antiga custa muito tempo).

Nossa única regalia é poder parar o sistema para realizar as modificações. O que segue abaixo, portanto, é um passo-a-passo de como transformar aquela primeira definição de matricula na segunda, garantindo que, após o procedimento:

• Todos os registros tenham um cd_artificial populado corretamente
• Qualquer código-fonte existente que manipule a tabela continue funcionando, pois não precisará saber da existência de cd_artifical
• Novos registros possam ser inseridos tanto a partir da aplicação web, quanto a partir do sql*plus, sem que um valor para o campo cd_artificial precise ser definido explicitamente.

Para ilustrar melhor o exemplo, apresento agora a definição de todas as 3 tabelas do sistema fictício, e também os registros inseridos para possibilitar o teste do procedimento.

create table estudante (
    cd_estudante int primary key,
    nm_estudante char(30)
);

create table curso (
    cd_curso int primary key,
    nm_curso char(30)
);

create table matricula (
    cd_estudante int references estudante (cd_estudante),
    cd_curso int references curso (cd_curso),
    nota number,
    constraint matricula_pk primary key (cd_estudante, cd_curso)
);

insert into estudante values (1, 'Caio');

insert into curso values (1, 'AWK');
insert into curso values (2, 'PHP');
insert into curso values (3, 'Python');
insert into curso values (4, 'Ruby');

insert into matricula values (1, 1, 5);
insert into matricula values (1, 2, 9);

Observe as colunas e os registros da tabela matricula, para poder comparar com a versão final:

SQL> select * from matricula;

CD_ESTUDANTE   CD_CURSO       NOTA
------------ ---------- ----------
           1          1          5
           1          2          9

Lembre-se de que os passos a seguir se aplicam somente ao Oracle. Se você estiver utilizando MySQL o procedimento é muito mais simples, e não envolve a criação nem de uma seqüência e nem de um gatilho. Provavelmente no PostgreSQL as coisas devem ser mais simples, mas não tenho o conhecimento necessário para dizer com certeza.

Vamos aos passos.

Passo 1 - Remover a restrição de chave primária de cdestudante,cdcurso

Para que a nova chave primária possa ser definida, a antiga deve ser eliminada.

alter table matricula drop constraint matricula_pk;

Passo 2 - Definir a restrição unique para cdestudante,cdcurso

A nossa antiga chave primária garantia que um mesmo aluno não poderia ser vinculado ao mesmo curso mais de uma vez. Como a chave foi eliminada, essa restrição de integridade não existe mais. Precisamos defini-la novamente.

alter table matricula add constraint matricula_pk unique(cd_estudante,cd_curso);

Passo 3 - Adicionar a coluna cd_artificial na tabela

Agora podemos adicionar a coluna que se tornará nossa nova chave primária.

alter table matricula add cd_artificial numeric(10);

Note que, neste momento, a coluna cd_artificial não possui valores para nenhum dos registros de matricula:

CD_ESTUDANTE   CD_CURSO       NOTA CD_ARTIFICIAL
------------ ---------- ---------- -------------
           1          1          5
           1          2          9

Passo 4 - Popular a coluna cd_artificial

Precisamos, portanto, popular a coluna recém-criada. Neste caso queremos atribuir valores a partir de 1 até o número total de registros da tabela.

update matricula set cd_artificial = rownum;

Veja o resultado:

CD_ESTUDANTE   CD_CURSO       NOTA CD_ARTIFICIAL
------------ ---------- ---------- -------------
           1          1          5             1
           1          2          9             2

Passo 5 - Adicionar a restrição de chave primária à coluna cd_artificial

Agora que a nova coluna já possui dados, ela pode se tornar nossa chave primária.

alter table matricula add constraint matricula_pk primary key (cd_artificial);

Passo 6 - Definir a seqüência que populará o campo cd_artificial

Campos auto incrementais não existem nativamente no Oracle da forma como existem no MySQL. Eles precisam ser definidos com um pouco mais de esforço: deve-se criar uma seqüência e um gatilho.

A seqüência fica responsável por guardar, de forma transacional, um número atual, e gerar um próximo número quando solicitada. Neste caso, como nossa tabela já possui 2 registros, precisamos que a seqüência inicie em 3:

create sequence matricula_seq start with 3;

Passo 7 - Popular o campo cd_artificial automaticamente

O gatilho (trigger) será responsável por interceptar a inserção de um registro na tabela matricula. Todo registro terá seu campo cd_artificial definido como matricula_seq.nextval antes da inserção (before insert), sem que seja preciso defini-lo explicitamente.

create or replace trigger matricula_pk_trig
   before insert
   on matricula
   for each row
begin
   select matricula_seq.nextval
   into :new.cd_artificial
   from dual;
end;
/ -- Esta contra-barra marca o fim da declaração da trigger.

Na verdade, é possível forçar um valor diferente de matricula_seq.nextval para o novo registro, desde que esse novo valor não viole a restrição de integridade da chave primária.

Para testar o gatilho, vamos inserir um novo registro que não especifica cd_artificial:

SQL> insert into matricula (cd_estudante,cd_curso,nota) values (1,3,7);

SQL> select * from matricula;

CD_ESTUDANTE   CD_CURSO       NOTA CD_ARTIFICIAL
------------ ---------- ---------- -------------
           1          1          5             1
           1          2          9             2
           1          3          7             3

Pronto! Agora o Rails será capaz de manipular essa tabela sem problemas.