ALTER TABLE
Mesmo que a modelagem de dados desde o início tenha sido feita com muita precisão e perfeição, a aplicação pode se modificar com o tempo e assim, possivelmente, será necessário alterar alguns aspectos das nossas tabelas no banco de dados. Hoje iremos tratar do que podemos fazer e como fazer com o comando ALTER TABLE.
Adicionando Colunas
Primeiro, o mais simples. Podemos adicionar colunas mesmo depois que a tabela esteja criada. Vejam o exemplo abaixo de como seria:
ALTER TABLE nome_da_table ADD
(nome_da_coluna DATATYPE [DEFAULT valor_default] [CONSTRAINT ...][, ....]);Vejam que a cláusula é bastante simples. Apenas as palavras reservadas ALTER TABLE seguindo do nome da tabela e depois a palavra reservada ADD logo após isto iremos descrever uma ou mais colunas, separadas por “,”, entre parênteses. Vejam que é bem similar, se não idêntico, a cláusula CREATE TABLE. A única observação que faço aqui para todos é que o valor DEFAULT deverá vir antes da declaração da CONSTRAINT, que esta última poderá ser nomeada ou não. Tudo que eu colocar entre [] será opcional. Os parênteses também é opcional, caso seja adicionado apenas uma coluna.
NOT NULL
Caso a intenção seja adicionar uma coluna com uma constraint NOT NULL, deverá ser observado alguns detalhes. Caso a tabela esteja vazia, ou seja, sem nenhuma linha, poderemos adicionar sempre problemas essa coluna. Mas caso ela contenha algumas linhas não poderemos fazer isto. Pois ao adicionarmos uma coluna, as linhas já existentes ficariam com o valor NULL nesta nova coluna. Se a coluna é NOT NULL isto não seria possível.
Para contornar esta situação, deveremos adicionar o valor DEFAULT que será colocado nas linhas já existentes.
Agora que já aprendemos como adicionar colunas, vejamos agora como modificar.
MODIFY
Pode ser que não queremos adicionar novas colunas, mas modificar as já existentes. Isto também pode ser feito com o comando ALTER TABLE. Veja, como é a sintaxe do comando abaixo:
ALTER TABLE nome_da_tabela MODIFY
(nome_da_coluna [DATATYPE][DEFAULT...][CONSTRAINT...][,...]);Diferentemente do ADD em que o DATATYPE é obrigatório, no MODIFY ele é opcional. Obrigatório é que temos que especificar pelo menos uma das 3 opções, o datatype, o valor default ou a constraint. Os parênteses, assim como em ADD, é opcional, caso seja modificada apenas uma coluna.
Devemos ter bastante cuidado ao modificar colunas na qual a tabela já contenha alguns dados. Já vimos mais acima o cuidado que devemos ter com a constraint NOT NULL. Vimos ao adicionar uma nova coluna, mas também se aplica a modificar uma já existente. A regre é que se a tabela estiver vazia podemos fazer qualquer alteração. Caso contrário dependerá da natureza do que queremos fazer. Para resumir, devemos entender muito bem todos os datatypes e constraints. Não devemos violar nenhuma regra e nem perder dados.
RENAME
Bem simples, e todos os itens são obrigatórios, veja:
ALTER TABLE nome_da_tabela RENAME COLUMN nome_antigo TO nome_novo;Única dica aqui é que RENAME tem a palavra reservada COLUMN, mas em ADD e MODIFY não. Não se esqueçam disso e nem se confundam, pois irão precisar lembrar disso em provas de certificação, como a SQL Expert.
DROP COLUMNS
Já vimos como adicionar e modificar colunas. Agora vamos ver como remover colunas.
Pode ser que em alguma ocasião específica uma coluna não é mais necessária, e podemos excluir ela liberando assim espaço e aumentando a performance desta tabela. Lembre-se sempre que ao remover uma coluna as suas constraints e índices também serão removidos. Veja duas formas de dar um DROP em uma ou mais coluna.
ALTER TABLE nome_da_tabela DROP COLUMN nome_da_coluna;
ALTER TABLE nome_da_coluna DROP (coluna1, coluna2, ...);A primeira opção tem a palavra reservada COLUMN, mas podemos dar um drop em apenas uma coluna. Já no segundo modo, podemos remover várias colunas, todas especificadas entre os parênteses e separadas por vírgula, a palavra reservada COLUMN não é usada nessa forma.
Não podemos remover todas as colunas de uma tabela, temos que deixar pelo menos uma. Assim como em modify, temos algumas restrições aqui também. Um exemplo seria uma coluna que tem o valor unique ou que seja uma PK e que alguns valores dessa coluna já seja referenciada por outra tabela em uma constraint FK. A restrição é que não podemos dar um drop nessa coluna, seria lançado um erro de execução. Pois outros registros dependem desse registro, tornando assim impossível o drop dessa coluna.
Mas existe uma solução, veja abaixo:
ALTER TABLE nome_da_tabela DROP COLUMN nome_da_colune CASCADE CONSTRAINT;Nessa solução, além de remover uma coluna que é referenciada pela constraint FK, a constraint FK sofrerá um drop também. Mas essa não seria a única solução, podemos deixar a constraint FK como DISABLED, mas isto nós veremos mais a frente.
UNUSED
UNUSED seria uma alternativa ao DROP da coluna. Talvez a tabela contenha milhares de linhas e a carga no servidor esteja alta. Então não seria uma boa ideia dar um DROP em uma coluna em um momento como este. Pois a performance do servidor sera bastante dregadada. A alternativa seria colocar a coluna como UNUSED. Em que ela não será mais utilizável e NUNCA mais poderemos utilizar essa coluna novamente. Qualquer índice ou constraint sofrerá um DROP. Podemos dar um DROP depois nesta coluna. A coluna configurada como UNUSED continua sendo contado como coluna no limite de 1000 por tabela. Para alterar uma ou mais colunas como UNUSED é bem simples, bem similar ao DROP, basta alterar a palavra reservada DROP por SET UNUSED, veja:
ALTER TABLE nome_da_tabela SET UNUSED COLUMN nome_da_coluna;
ALTER TABLE nome_da_tabela SET UNUSED (coluna1, coluna2...);Para dar um DROP nessas colunas é só dar o seguinte comando:
ALTER TABLE nome_da_tabela DROP UNUSED COLUMNS;ADD CONSTRAINTS
No meu blog já mostrei como adicionar constraints no momento de criação de uma tabela. Agora aqui veremos como adicionar constraints depois de a tabela já ter sido criada. Vamos ver como seria adicionar constraints INLINE:
ALTER TABLE nome_da_tabela MODIFY mome_da_colune PRIMARY KEY;
ALTER TABLE nome_da_tabela MODIFY mome_da_colune CONSTRAINT nome_da_constraint PRIMARY KEY;Agora Out of Line:
ALTER TABLE nome_da_table ADD CONSTRAINT nome_da_constraint PRIMARY KEY(nome_da_coluna);Algo que devemos sempre nos lembrar ao trabalhar com constraints, é que nunca podemos adicionar NOT NULLa no modo Out of Line. Então só iremos conseguir dando um MODIFY na coluna e nunca por ADD CONSTRAINT.
DROP CONSTRAINT
Vejamos como dar drop em constraints agora. As mais simples são as constraints PK e UNIQUE, pois não precisamos do nome da constraint, apenas das colunas, isso para UNIQUE, para PK nem o nome da coluna precisamos. Veja:
ALTER TABLE nome_da_tabela DROP PRIMARY KEY [options];
ALTER TABLE nome_da_tabela DROP UNIQUE(colunas) [options];No caso de uma PK, como só existe uma por tabela, não precisamos especificar qual é a coluna. Já para uma constraint UNIQUE devemos especificar qual ou quais colunas fazem parte dessa constraint. As opções seria CASCADE, para dropar também uma possível FK e a opção KEEP INDEX ou DROP INDEX para remover ou não o índice junto. As opções default são DROP INDEX e NOT CASCADE.
Para outras constraints seria a seguinte sintaxe:
ALTER TABLE nome_da_tabela DROP CONSTRAINT nome_da_constraint [CASCADE];A única observação a ser fazer é que não conseguimos dar um DROP em uma constraint NOT NULL, para remover essa restrição devemos fazer da seguinte maneira:
ALTER TABLE nome_da_tabela MODIFY nome_da_coluna NULL;DISABLE AND ENABLE CONSTRAINTS
Pode ser possível que seja necessário desativar uma constraint para fazermos uma terefa no banco de dados. Como importar um grande volume de dados. Talvez façamos isso para impactar menos o possível na performance ou por que os dados que queremos importar não estão de acordo com as constraints. Bem, isto é apenas um exemplo. Vejamos como fazer isto então:
ALTER TABLE nome_da_tabela ENABLE | DISABLE [VALIDATE | NOVALIDATE] OPTIONS;As opções seriam:
- PRIMARY KEY.
- UNIQUE(COLUNAS).
- CONSTRAINT nome_da_constraint.
Como já vimos anteriormente, só existe uma PK por tabela, então não precisamos especificar a coluna ou as colunas que componhem essa constraint. Já em UNIQUE é necessário. Se for outro tipo de constraint devemos especificar o nome da constraint.
Essa não é a única maneira de desabilitar uma constraint, veja outro modo:
ALTER TABLE nome_da_tabela MODIFY OPTIONS ENABLE | DISABLE;As opções são as mesmas que vimos acima. Claro, que não podemos desabilitar uma PK se ela é referenciada em outra tabela por uma FK. Para contornar essa situação podemos colocar CASCADE no final do nosso comando e assim ambas as constraints serão desabilitadas. Mas, isto só funciona no DISABLE, para ENABLE devemos fazer manualmente em ambas as constraints. O mesmo é verdade se quisermos dar um DROP em uma tabela. Se existir uma FK referenciando alguma coluna dessa tabela, o DROP irá falhar, mesmo se a constraint FK estiver DISABLE. Devemos utilizar no final do nosso DROP as palavras reservadas CASCADE CONSTRAINTS.
Talvez agora estejam se perguntando o que VALIDATE e NOVALIDATE fazem. Bem, isto é bem importante. Existem 4 combinações possíveis, que são:
- ENABLE VALIDATE – Valida a constraints nas linhas já existentes. Mesma coisa que apenas ENABLE.
- ENABLE NOVALIDATE – Não valida a constraints nas linhas já existentes, somente nas novas linhas que serão inseridas.
- DISABLE VALIDATE – Se a constraint tiver um índice associado ele sofrerá um DROP.
- DISABLE NOVALIDATE – Mesma coisa que DISABLE. Apenas desabilita a constraint.
ON DELETE
Vimos mais acima algumas alternativas para lidar com situações onde precisamos deletar algumas linhas de uma dada tabela, mas que ela é referenciada por outra tabela através de uma constraint FK. Podemos desabilitar a constraint ou até mesmo remover ela por meio de um DROP. Mas essas não são as únicas alternativas, vejamos outras duas agora.
A primeira alternativa seria ON DELETE CASCADE. Essa configuração é feita na criação de uma constraint. Por exemplo:
ALTER TABLE nome_da_table ADD CONSTRAINT nome_da_constraint FOREIGN KEY(nome_da_coluna)
REFERENCES nome_da_tabela_referenciada (nome_da_coluna_referenciada) ON DELETE CASCADE;Fazendo isso quando deletarmos a linha na qual ele referencia, ela também será deletada. Mas talvez isso não seja desejado. Então a segunda opção seria mudar o CASCADE para SET NULL. Assim a nossa linha terá NULL nessa coluna caso a linha que ela referencia seja excluída.
DEFERRABLE e DEFERRED
Já vimos que podemos desabilitar as constraints e assim podemos fazer algo que com elas seria impossível, mas logo depois devemos ativar elas novamente para que tudo fique de maneira organizada e consistente. O Oracle nos dá mais uma alternativa de fazer isso, que é o título desse tópico. Com isto podemos desativar as constraints até se completar a transaction e depois elas serão ativadas novamente automaticamente.
Primeiro devemos alterar a constraint para que ela seja DEFERRABLE, ou seja, para que possamos depois desativar ela temporariamente. Fazemos isto do seguinte modo:
ALTER TABLE nome_da_table ADD CONSTRAINT nome_da_constraint FOREIGN KEY(nome_da_coluna)
REFERENCES nome_da_tabela2(nome_da_coluna2) DEFERRABLE;Acima, adicionamos uma coluna DEFERRABLE, o padrão é NOT DEFERRABLE. Com essa opção ativada na coluna, podemos desativar ela temporariamente até a transação concluir.
SET CONSTRAINT nome_da_constraint DEFERRED;Ou
SET CONSTRAINT ALL DEFERRED;Essa modificação persiste somente até o próximo commit ou se modificarmos manualmente da seguinte maneira:
SET CONSTRAINT nome_da_constraint IMMEDIATE;Ou
SET CONSTRAINT ALL IMMEDIATE;RENAME CONSTRAINTS
Esse será o último assunto desse artigo, que é sobre renomear constraints. Podemos modificar isso de uma maneira bem simples, conforme demonstrado abaixo:
ALTER TABLE nome_da_tabela RENAME CONSTRAINT nome_antigo TO nome_novo;Referência
- Livro – OCA Oracle Database 12c: SQL Expert Exam Guide (Exam 1Z0-047), Steve O´Hearn – Oracle Press – Mc Graw Hill
Abraço
