Criando tabelas

Para criar uma tabela, é necessário que o usuário tenha privilégio e uma área para armazenamento. A sintaxe simplificada para criação de tabelas é:

CREATE TABLE [esquema.] tabela
(nome da coluna tipo do dado [DEFAULT expr]
[constraint da coluna],
...,
[constraint da tabela]);

• Esquema: É o nome do proprietário da tabela; quando omitido, a tabela é criada no esquema do usuário corrente.
• Tabela: É o nome da tabela. DEFAULT expr: Especifica um valor default que será utilizado quando um dado for omitido na inserção.
• Coluna: É o nome da coluna.
• Tipo de dados: É o tipo de dados e o comprimento da coluna.
• Constraint: Esta cláusula é opcional e especifica as restrições para a coluna ou para a tabela; quando o nome da constraint é omitido, o Oracle assume uma identificação.

Convenções para a nomeação de tabelas e colunas:

• Deve começar com uma letra;
• Pode ter de 1 a 30 caracteres;
• Deve conter somente A–Z, a–z, 0–9, _, $ e #;
• Não deve duplicar o nome de outro objeto de propriedade do mesmo usuário;
• Não deve ser uma palavra reservada pelo SBGDR.

Tipo de dados:

1. VARCHAR2 (tamanho):  Dados de caractere de comprimento variável;
2. CHAR (tamanho):  Dados de caractere de comprimento fixo;
3. NUMBER (p,s):  Dados numéricos de comprimento variável;
4. DATE:  Valores de data e hora;
5. LONG:  Dados de caractere de comprimento variável até 2 gigabytes;
6. CLOB:  Dados de caractere de um byte de até 4 gigabytes;
7. RAW e LONG RAW:  Dados binários brutos;
8. BLOB:  Dados binários de até 4 gigabytes;
9. BFILE:  Dados binários armazenados em um arquivo externo de até 4 gigabytes.

Exemplo:

SQL>CREATE TABLE dept
2 (deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13));
Table created.

A instrução describe é utilizada para exibir a estrutura de uma tabela.

SQL> DESCRIBE dept
Name Null? Type
------------------ -------- ---------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

Restrições (constraints)

As restrições impõem regras que podem ser no nível da coluna ou no nível da tabela. São utilizadas para impedir que dados inválidos sejam digitados nas tabelas, garantindo assim a consistência dos dados.
Os seguintes tipos de restrição são válidos no Oracle:

• NOT NULL – Impõe a inserção obrigatória de dados nas colunas com essa restrição;
• UNIQUE – Campos com essa restrição não aceitam dados com valores já inseridos em outros registros;
• PRIMARY KEY – Define uma ou mais colunas como chave primária da tabela;
• FOREIGN KEY – Define uma ou mais colunas como chave estrangeira da tabela;
• CHECK – Especifica uma lista de valores que serão utilizados para validar a inserção de um dado.

Restrição NOT NULL – Valor Obrigatório

A restrição NOT NULL assegura que os valores nulos não sejam permitidos na coluna. As colunas sem uma restrição NOT NULL podem conter valores nulos por default. Deve ser definida no nível da coluna.
Exemplo: no exemplo acima, a restrição NOT NULL está sendo aplicada às colunas ENAME e DEPTNO da tabela EMP. Observe que na linha 3 a restrição está sendo identificada; já na linha 9 não, o Oracle a identificará de acordo com o seu padrão de identificação SYS_cn.

SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) constraint emp_ename_NN NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(7,2) NOT NULL);

Para verificar se as colunas da tabela estão ou não com a restrição NOT NULL, utilize a instrução DESCRIBE, como segue exemplo abaixo:

SQL> DESCRIBE EMP;
Name Null? Type
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NOT NULL NUMBER(2)

Restrição UNIQUE KEY – Valor Único

Uma restrição de integridade UNIQUE KEY requer que cada valor em uma coluna ou conjunto de colunas (chave) sejam exclusivos — ou seja, duas linhas de uma tabela não podem ter valores duplicados em uma coluna específica ou conjunto de colunas. A coluna (ou conjunto de colunas) incluída na definição da restrição UNIQUE KEY é chamada de chave exclusiva. Se a chave UNIQUE contiver mais de uma coluna, tal grupo de colunas é considerado uma chave exclusiva composta. Exemplo: tabela dept cujo nome do departamento (dname) não poderá ser duplicado:

SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE(dname));

Restrição PRIMARY KEY – Chave Primária

Uma restrição PRIMARY KEY cria uma única chave primária para cada tabela. A restrição PRIMARY KEY é uma coluna ou conjunto de colunas que identifica exclusivamente cada linha em uma tabela. Essa restrição impõe a exclusividade da coluna ou combinação de colunas e assegura que nenhuma coluna que seja parte da chave primária possa conter um valor nulo. Exemplo: tabela dept cujo campo número do departamento (deptno) deverá ser a chave de identificação do registro:

SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE (dname),
6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

Chave Primária Composta

SQL> CREATE TABLE pk_composta(
2 valor1 NUMBER(2),
3 valor2 NUMBER(2),
4 CONSTRAINT dept_deptno_pk PRIMARY KEY(valor1, valor2));

Restrição FOREIGN KEY – Chave Estrangeira

É uma restrição de integridade referencial, designa uma coluna ou combinação de colunas como a chave estrangeira e estabelece um relacionamento entre a chave primária, ou uma chave exclusiva, na mesma tabela ou em uma tabela diferente. Um valor de chave estrangeira deve corresponder a um valor existente na tabela mãe ou ser NULL. As chaves estrangeiras são baseadas nos valores dos dados, sendo puramente lógicas, e não ponteiros físicos. Exemplo: o DEPTNO definido como a chave estrangeira na tabela EMP (tabela filha ou dependente); ela faz referência à coluna DEPTNO da tabela DEPT (tabela mãe ou referenciada).

SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(7,2) NOT NULL,
10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11 REFERENCES dept (deptno) );

Restrição CHECK – Verificação de Valores

Define uma condição que cada registro deve atender. Podem ser utilizados operadores de comparação para a delimitação dos valores a serem aceitos para a coluna. Exemplo: para evitar erros de digitação do usuário, você coloca uma restrição do tipo CHECK para o campo SALÁRIO, em que o mesmo deve ser maior do que o salário mínimo de R$800,00. Dessa forma, ao tentar inserir valor menores do que 800, a restrição será ativada e será apresentado um erro para o usuário.

SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 constraint emp_sal_ck check (sal > 800));

Consultando Restrições de uma Tabela

Restrições definidas para a tabela emp:

SQL>SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,
2 STATUS,SEARCH_CONDITION
3 FROM USER_CONSTRAINTS
4 WHERE TABLE_NAME = ‘EMP’;

• CONSTRAINT_NAME: é o nome da restrição;
• CONSTRAINT_TYPE: tipos de restrições aos quais os campos estão envolvidos:
  • C = NOT NULL;
  • P = PRIMARY KEY;
  • R = FOREIGN KEY;
  • U = UNIQUE KEY;
• STATUS: representa o estado em que a restrição se encontra:
• ENABLE, significa que a restrição está válida e está sendo usada;
• DISABLE, que a restrição está desabilitada e que por isso não está em uso.
• SEARCH_CONDITION: é a condição expressa da restrição.

CONSTRAINT_NAME C STATUS SEARCH_CONDITION
---------------- - ------- -------------------
SYS_C00884 C ENABLED “EMPNO” IS NOT NULL
SYS_C00885 C ENABLED “DEPTNO” IS NOT NULL
EMP_EMPNO_PK P ENABLED
EMP_MGR_FK R ENABLED
EMP_DEPTNO_FK R ENABLED
EMP_ENAME_UK U ENABLED

Alterando uma tabela

Depois das tabelas terem sido criadas, pode ser necessário alterar suas estruturas para acrescentar uma nova coluna, para redefinir a estrutura de uma coluna, para adicionar ou alterar uma restrição. Para isso, pode ser utilizada a instrução ALTER TABLE.

Adicionando colunas

Para adicionar colunas, deve-se utilizar a cláusula add em conjunto com a instrução ALTER TABLE

ALTER TABLE tabela
Add (nome da coluna tipo de dado [restrições], ...);

• Tabela: Nome da tabela que irá ser alterada para possuir a coluna ou restrição especificada.
• Nome da coluna: Nome da coluna que será acrescentada.
• Tipo de dado: Tipo de dado da coluna que será acrescentada.
• Restrições: Especificação das restrições para a coluna acrescentada, se for necessário.

Exemplo: Adicionar a coluna hobby, alfanumérica com 15 posições, à tabela EMP.

SQL> ALTER TABLE emp
2 ADD (hobby VARCHAR2(15));

• A nova coluna torna-se a última coluna na tabela;
• Se uma tabela já contiver linhas quando uma coluna for adicionada, então a nova coluna será inicialmente nula para todas as linhas.

Adicionando Restrições

Para adicionar colunas, deve-se utilizar a cláusula add em conjunto com a instrução ALTER TABLE

ALTER TABLE tabela
ADD [CONSTRAINT restrição] tipo (coluna);

• Tabela: é o nome da tabela;
• Restrição: é o nome da restrição, é opcional mas recomendado;
• Tipo: é o tipo da restrição;
• Coluna: é o nome da coluna afetada pela restrição.

Exemplo: adicionar uma restrição FOREIGN KEY à tabela EMP, indicando que um gerente já deve existir como um funcionário válido na tabela EMP.

SQL> ALTER TABLE emp
2 ADD CONSTRAINT emp_mgr_fk
3 FOREIGN KEY(mgr) REFERENCES emp(empno);

• Restrições não podem ser modificas; mas, para adicionar uma restrição NOT NULL, pode-se usar a cláusula MODIFY;
• Uma coluna pode ser especificada como NOT NULL somente se a tabela não contiver linhas.

Modificando Colunas

Para modificar a estrutura das colunas, deve-se utilizar a cláusula modify em conjunto com a instrução ALTER TABLE.

ALTER TABLE tabela
MODIFY (coluna especificações);

• Tabela: Nome da tabela.
• Coluna: Nome da coluna que sofrerá modificações.
• Especificações: Tipo de dado, tamanho, restrições.

Exemplo: Alterar o tamanho da coluna ename da tabela EMP para 30 posições.

SQL> ALTER TABLE EMP
2 MODIFY (ename VARCHAR2(30));

É possível:

• aumentar a largura ou precisão de uma coluna numérica;
• diminuir a largura de uma coluna se contiver somente valores nulos e/ou se a tabela não tiver linhas;
• alterar o tipo de dados se a coluna contiver valores nulos;
• converter uma coluna CHAR para o tipo de dados VARCHAR2, ou vice-versa, se a coluna contiver valores nulos ou se o tamanho da coluna não for alterado.

Eliminando uma Restrição

Para remover uma restrição, deve-se utilizar a cláusula drop constraint em conjunto com a instrução ALTER TABLE.

ALTER TABLE tabela
DROP PRIMARY KEY | UNIQUE (coluna) |
CONSTRAINT restrição [CASCADE];

• Tabela: Nome da tabela.
• Coluna: É o nome da coluna afetada pela restrição.
• Restrição: Nome da constraint que será eliminada.
• Cascade: Está cláusula elimina todas as restrições dependentes.

Exemplo: remover a restrição PRIMARY KEY na tabela DEPT e eliminar a restrição FOREIGN KEY associada na coluna EMP.DEPTNO.

SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;    

Para descobrir o nome da restrição, pode-se consultar as views1 do dicionário de dados USER_CONSTRAINTS ou USER_CONS_COLUMNS. Antes de executar a consulta, verifique os nomes das colunas com a instrução DESCRIBE.

Exemplo:

SELECT CONSTRAINT_NAME, COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = ‘EMP’;

Resultado:

CONSTRAINT_NAME COLUMN_NAME
EMP_DEPTNO_FK DEPTNO
EMP_EMPNO_PK EMPNO
EMP_ENAME_UK ENAME
EMP_MGR_FK MGR
SYS_C00884 EMPNO
SYS_C00885 DEPTNO

Eliminando uma Tabela

A instrução DROP TABLE remove a definição de uma tabela. Quando uma tabela é eliminada, o banco de dados perde todos os dados na tabela e todos os índices associados a ela.

DROP TABLE tabela;

Exemplo: Eliminar a tabela dept30.

SQL> DROP TABLE dept30;

Ao eliminar uma tabela:

• Todos os dados são deletados da tabela;
• As views e sinônimos permanecerão, mas serão inválidos;
• Todas as transações pendentes sofrerão commit;
• Somente o criador da tabela ou um usuário com o privilégio DROP ANY TABLE poderá remover uma tabela.

A instrução DROP TABLE, uma vez executada, é irreversível. O Oracle Server não questiona a ação quando você emite a instrução DROP TABLE. Se você possuir tal tabela ou tiver um privilégio de nível superior, então a tabela será imediatamente removida.