Como alterar o tamanho de uma coluna CHAR que é chave estrangeira

Quando uma coluna do tipo CHAR cujo valor é referenciado noutras tabelas como chave estrangeira necessita de um aumento no seu tamanho podemos deparar-nos com um obstáculo, porque existirão constraints na base de dados que serão quebradas com o aumento do tamanho máximo da coluna CHAR.

Uma coluna do tipo CHAR é fisicamente armazenada com o mesmo tamanho independentemente do número de caracteres que guarda, assim, ao alterar o tamanho de uma coluna CHAR de 5 para 15 está-se a alterar o próprio valor da coluna e não a expandir o tamanho máximo que pode ser guardado na coluna (este comportamento é dado pelo tipo VARCHAR2).  Com o aumento do valor da coluna todas as outras colunas que referenciam a coluna alterada necessitam também dever o seu tamanho alterado, mas o seu tamanho não pode ser modificado porque as constraints das chaves estrangeiras da base de dados não deixam. Portanto o que tem de ser feito é desligar as constraints antes de alterar o tamanho da coluna, para depois voltarem a ser activadas.

drop table kaka;
drop table kaka_ind;
drop table koko; 

-- criar as tabelas de testes e as suas relações
create table koko (

 col_a char(5),
 col_b numeric,

 primary key (col_a)

);

-- criar a tabela dois
create table kaka (

 col_a_a char(5),
 col_b_b numeric,

 col_fk char(5),

 primary key (col_a_a),
 constraint ghg_ds foreign key (col_fk) references koko(col_a)

);

create table kaka_ind (

 col_a_a char(5),
 col_b_b numeric,

 col_fk char(5),

 primary key (col_a_a),
 constraint kaka_ind_ghg_ds foreign key (col_fk) references koko(col_a)

);

create index kak_i on kaka_ind(col_fk);

-- inserir o texto ok
insert into koko (col_a, col_b) values ('00001', 1);
insert into  koko (col_a, col_b) values ('00002', 1);
insert into  koko (col_a, col_b) values ('00003', 1);
insert into  koko (col_a, col_b) values ('00004', 1);
insert into  koko (col_a, col_b) values ('00005', 1);
insert into  koko (col_a, col_b) values ('00006', 1);
insert into  koko (col_a, col_b) values ('00007', 1);
insert into  koko (col_a, col_b) values ('7', 1);

insert into  kaka (col_a_a, col_b_b, col_fk) values ('10001', 1, '00001');
insert into  kaka (col_a_a, col_b_b, col_fk) values ('10002', 1, '00002');
insert into  kaka (col_a_a, col_b_b, col_fk) values ('10003', 1, '00003');
insert into  kaka (col_a_a, col_b_b, col_fk) values ('10004', 1, '00004');
insert into  kaka (col_a_a, col_b_b, col_fk) values ('10005', 1, '00004');
insert into  kaka (col_a_a, col_b_b, col_fk) values ('10006', 1, '00005');
insert into  kaka (col_a_a, col_b_b, col_fk) values ('10007', 1, '7');

insert into  kaka_ind (col_a_a, col_b_b, col_fk) values ('10001', 1, '00001');
insert into  kaka_ind (col_a_a, col_b_b, col_fk) values ('10002', 1, '00002');
insert into  kaka_ind (col_a_a, col_b_b, col_fk) values ('10003', 1, '00003');
insert into  kaka_ind (col_a_a, col_b_b, col_fk) values ('10004', 1, '00004');
insert into  kaka_ind (col_a_a, col_b_b, col_fk) values ('10005', 1, '00004');
insert into  kaka_ind (col_a_a, col_b_b, col_fk) values ('10006', 1, '00005');
insert into  kaka_ind (col_a_a, col_b_b, col_fk) values ('10007', 1, '7');

select col_a, length(col_a), col_b, length(col_b) from koko;
select col_a_a, length(col_a_a), col_b_b, length(col_b_b), col_fk, length(col_fk) from kaka;
select col_a_a, length(col_a_a), col_b_b, length(col_b_b), col_fk, length(col_fk) from kaka_ind;

-- deve dar erro: por causa dos valores nas chaves estrangeiras
alter table koko  modify col_a char(15);

select * from ALL_CONSTRAINTS where TABLE_NAME = 'KAKA'

select * from ALL_CONS_COLUMNS where TABLE_NAME = 'KAKA'

-- determinar as constraints das chaves estrangeiras de uma chave privada 
select * from ALL_CONS_COLUMNS
where
(table_name, constraint_name) in (

select cons.table_name, cons.constraint_name
from ALL_CONSTRAINTS cons
where cons.constraint_type = 'R'
and r_constraint_name in
(select constraint_name from ALL_CONSTRAINTS where table_name = 'KOKO' and  constraint_type = 'P')
) order by position

-- desligar as constraints das chaves estrangeiras
ALTER TABLE kaka DISABLE CONSTRAINT ghg_ds KEEP INDEX;   

ALTER TABLE kaka_ind DISABLE CONSTRAINT KAKA_IND_GHG_DS KEEP INDEX;   

-- modificar o tamanho das colunas
alter table koko  modify col_a char(15);

alter table kaka modify col_fk char(15);

alter table kaka_ind modify col_fk char(15);

-- voltar a ligar as constraints. Primeiro no estado NOVALIDATE para  que as novas linhas sejam sujeitas às constraints mas, as antigas não

ALTER TABLE kaka ENABLE NOVALIDATE CONSTRAINT ghg_ds;

ALTER TABLE kaka_ind ENABLE NOVALIDATE CONSTRAINT KAKA_IND_GHG_DS;

-- ligar definitivamente as constraints para todas as linhas das tabelas
ALTER TABLE kaka ENABLE CONSTRAINT ghg_ds;

ALTER TABLE kaka_ind ENABLE CONSTRAINT KAKA_IND_GHG_DS;

-- Confirmar se as constraints estão efectivamente ligadas
select col_a, length(col_a), col_b, length(col_b) from koko;
select col_a_a, length(col_a_a), col_b_b, length(col_b_b), col_fk, length(col_fk) from kaka;
select col_a_a, length(col_a_a), col_b_b, length(col_b_b), col_fk, length(col_fk) from kaka_ind;

-- tem de dar erro, porqueo valor para a chave já existe
insert into  koko (col_a, col_b) values ('7', 1);
-- tem de dar erro: porque col_fk não já existe na tabela koko
insert into  kaka (col_a_a, col_b_b, col_fk) values ('10009', 1, '79');

insert into  kaka_ind (col_a_a, col_b_b, col_fk) values ('10009', 1, '79');

-- os inserts devem agora funcionar
insert into  koko (col_a, col_b) values ('8', 1);
insert into  kaka (col_a_a, col_b_b, col_fk) values ('10010', 1, '8');
insert into  kaka_ind (col_a_a, col_b_b, col_fk) values ('10010', 1, '8');

Referência:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/general005.htm#CHDFEJII