Oracle - Pl/SQL Alter Table (Variações sobre o tema): Modify, Add Constraint, Add, Drop e Rename

Com o comando “ALTER TABLE” podemos realizar diversas modificações estruturais em tabelas de banco de dados. A seguir, experimentaremos como isso pode ser feito em PL/SQL.

Trabalharemos com a tabela exemplificada abaixo:

CREATE TABLE ENDERECO_ZN
(
 CODENDERECO_ZN NUMBER(10) NOT NULL,
 ID_TIPO_ENDERECO           NUMBER(10),
 LOGRADOURO                 VARCHAR2(100),
 COMPLEMENTO                VARCHAR2(50),
 BAIRRO                     VARCHAR2(50),
 CIDADE                     VARCHAR2(50),
 ESTADO                     VARCHAR2(2),
 CEP                        VARCHAR2(10),
 NUMERO                     VARCHAR2(10)
);

A primeira coisa que vou fazer é alterar o nome de uma das colunas:

Alterando o nome da coluna: De “CODENDERECO_ZN” para “ENDERECO_ZNID”

ALTER TABLE
  ENDERECO_ZN
RENAME COLUMN
  CODENDERECO_ZN
TO
  ENDERECO_ZNID

Agora vamos alterar algumas outras coisas numa coluna. Que tal o tamanho e torná-la obrigatória?

-- Alterando o tamanho da coluna e tornando-a obrigatória

ALTER TABLE ENDERECO_ZN MODIFY COMPLEMENTO varchar2(150) not null;

Veja como está a nossa tabela após essas primeiras alterações ...

-- Create table
create table ENDERECO_ZN
(
 ENDERECO_ZNID    NUMBER(10) not null,
 ID_TIPO_ENDERECO NUMBER(10),
 LOGRADOURO       VARCHAR2(100),
 COMPLEMENTO      VARCHAR2(150) not null,
 BAIRRO           VARCHAR2(50),
 CIDADE           VARCHAR2(50),
 ESTADO           VARCHAR2(2),
 CEP              VARCHAR2(10),
 NUMERO           VARCHAR2(10)
);

Podemos aplicar modificações em mais de uma coluna no mesmo comando. Veja ilustrado abaixo como fazer:

-- Alterando mais de uma coluna no mesmo comando
ALTER TABLE ENDERECO_ZN MODIFY (COMPLEMENTO varchar2(170) null, LOGRADOURO varchar2(130) not null);

No exemplo acima alteramos tanto o tamanho das duas colunas, quanto a obrigatoriedade das mesmas. Aprimeira definimos para aceitar valores nulos e a segunda para não aceitar. Vejamos como ficou a tabela “ENDERECO_ZN”.

create table ENDERECO_ZN
(
 ENDERECO_ZNID    NUMBER(10) not null,
 ID_TIPO_ENDERECO NUMBER(10),
 LOGRADOURO       VARCHAR2(130) not null,
 COMPLEMENTO      VARCHAR2(170),
 BAIRRO           VARCHAR2(50),
 CIDADE           VARCHAR2(50),
 ESTADO           VARCHAR2(2),
 CEP              VARCHAR2(10),
 NUMERO           VARCHAR2(10)
);

Podemos também adicionar mais de uma coluna com o mesmo comando
-- Adicionando novas colunas

Alter table ENDERECO_ZN
ADD ( Pais   varchar2(50),
    DataCadastro        Timestamp not null);

Mais uma vez, a tabela ENDERECO_ZN refletindo o comando anterior:

     
create table ENDERECO_ZN
(
 ENDERECO_ZNID    NUMBER(10) not null,
 ID_TIPO_ENDERECO NUMBER(10),
 LOGRADOURO       VARCHAR2(130) not null,
 COMPLEMENTO      VARCHAR2(170),
 BAIRRO           VARCHAR2(50),
 CIDADE           VARCHAR2(50),
 ESTADO           VARCHAR2(2),
 CEP              VARCHAR2(10),
 NUMERO           VARCHAR2(10),
 PAIS             VARCHAR2(50),
 DATACADASTRO     TIMESTAMP(6) not null
)

Supondo que a coluna “Pais” não seja adequada ao modelo de negócio em questão, vamos excluí-la da tabela.

-- Dropnado uma coluna
Alter table ENDERECO_ZN drop column Pais

Agora vamos alterar o tipo de dado de uma das colunas

-- Alterando o tipo de dado de uma coluna
ALTER TABLE ENDERECO_ZN MODIFY DATACADASTRO Date;

Outra vez, alteramos a tabela ...

create table ENDERECO_ZN
(
 ENDERECO_ZNID    NUMBER(10) not null,
 ID_TIPO_ENDERECO NUMBER(10),
 LOGRADOURO       VARCHAR2(130) not null,
 COMPLEMENTO      VARCHAR2(170),
 BAIRRO           VARCHAR2(50),
 CIDADE           VARCHAR2(50),
 ESTADO           VARCHAR2(2),
 CEP              VARCHAR2(10),
 NUMERO           VARCHAR2(10),
 DATACADASTRO     DATE not null
)

O Comando “ALTER TABLE” também é uma forma de definirmos constraits. Vejamos ...

Definindo uma Chave primária: Suponha que você esta trabalhando com uma tabela que foi crianda a muitos anos por outros programadores. Esses programadores não definiram nenhuma coluna como chave primária. Mas hoje, você precisa fazer isso. Hoje você precisa que esta tabela possua uma PK. Vaja uma forma de fazer isso ilustrada abaixo:

-- Pk
alter table ENDERECO_ZN add primary key  (ENDERECO_ZNID);

Note que eu preparei a tabela para estar relaciona a outra. A coluna “ID_TIPO_ENDERECO” deveria ser uma chave estrangeira para uma tabela que armazenasse os tipos de Endereço. Esse tipo de modelagem atende a uma normalização muito comum. Portanto, antes de declararmos a chave estrangeira vamos criar a tabela de tipo de endereço:

-- Crinado a tabela de Tipo de Endereço
Create Table Tipo_EnderecoZN (
  ID_TIPO_ENDERECO NUMBER(10) not null,
  Tipo_Endereco Varchar2(100) not null
);

alter table Tipo_EnderecoZN add primary key  (ID_TIPO_ENDERECO);

Na linha 7 do trecho de código acima, repeti o mesmo comando, que usei para criar a chave primária para tabela Enreco_ZN, para definir a coluna “ID_TIPO_ENDERECO” como PK (Primary Key – Chave primária) em “Tipo_EnderecoZN”.

Agora vamos criar a constraint que garantirá a integridade referencial entre as tabelas:

Alter table Endereco_ZN add constraint FK_Endereco_TpEndereco foreign key (ID_TIPO_ENDERECO) references Tipo_EnderecoZN (ID_TIPO_ENDERECO);

Qualidade

Para garantir a qualidade dos dados que serão persistidos nessas tabelas podemos definir regras de validação dos dados (Veja mais sobre isso em “Banco de Dados ou Bando de Dados? ”).

-- Definido regras de validação de dados - checks
Alter table Tipo_EnderecoZN Add CONSTRAINT chk_Tipo_Endereco CHECK (LENGTH(Tipo_Endereco) > 5);
Alter table Endereco_ZN Add CONSTRAINT chk_DATACADASTRO CHECK (DATACADASTRO > TO_DATE('01/01/2000', 'DD/MM/YYYY'));
Alter table Endereco_ZN Add CONSTRAINT chk_BAIRRO CHECK (LENGTH(BAIRRO) > 3);
Alter table Endereco_ZN Add CONSTRAINT chk_ESTADO CHECK (LENGTH(ESTADO) > 1);

Qunado for necessário "dropar" uma constraint ...

ALTER table Endereco_ZN drop constraint FK_Endereco_TpEndereco;

Observe que não referenciei o nome da constraint entre plics.

Obviamente, você deve abusar de ser criterioso ao definir uma regra como essas. Visto que, se ela não definida com exatidão em total harmonia com as regras de negócio (da empresa, para qual o sistema gerenciador de banco de dados está sendo desenvolvido) você terá sérios problemas. Por exemplo, com relação ao trecho de código anterior:
Na linha 1 definimos que na tabela Tipo_EnderecoZN, a coluna Tipo_Endereco não vai aceitar nenhum valor cujo número de caracteres seja menor que 5. Portanto, se tentarem inserir o valor “Resid” (abreviação de “Residencial”) será frustrado. Isso tanto pode ser muito útil, evitando que valores lixo sejam inseridos no banco, mas pode ser prejudicial caso exista alguma possibilidade de uma valor de, por exemplo, três caracteres precise ser inserido na coluna em questão.
Na linha 2, definimos um valor mínimo para a coluna “DataCadastro”, da tabela “ENDERECO_ZN”. Isso vai evitar valores de data do tipo “01/02/0009”. Esse valor de data não é uma valor inválido, entretanto pode causar problema.