Constraints

Constraints

(Regras para o preenchimento de colunas)

 

Autor: Prof. Paulo Kaupa

 

Este texto está dividido da seguinte forma:

• Conceito de Constraints.
• Tipos de Constrants existentes no Oracle.
• Criação de Tabela com Constraint.
• Sintaxe para adicionar uma Constraint a uma Tabela.
• Exemplos.
• Consultar Constraints de uma Tabela.
• Exclusão de Constraints.
• Exercícios.

 

As Contraints são restrições (regras) atribuídas a uma determinada coluna de uma tabela criada no banco de dados.  Com o uso das Constraints é possível garantir que o conteúdo da coluna seja preenchido de acordo com o esperado. No decorrer deste texto exemplos serão mostrados para facilitar o entendimento deste importante conceito usado na programação para Banco de Dados.

 

Tipos de Constraints existentes no Oracle

 

No Banco de Dados Oracle as Constraints podem ser de um dos diferentes tipos a seguir:

NOT NULL – Não permite que uma coluna da tabela não seja preenchida.

UNIQUE – Esta Constraint impede que o valor de uma coluna se repita em outras linhas da tabela, ou seja, é uma restrição exclusiva. Exemplo:

               Em uma tabela de Produtos, para garantir a integridade dos dados inseridos, podemos restringir a coluna NOME_PRODUTO para não aceitar valores duplicados, impedindo desta forma que um mesmo produto seja cadastrado mais que uma vez.

PRIMARY KEY  – Determina que uma coluna não pode ser deixada em branco e nem ter valores duplicados. É uma combinação da Constraint NOT NULL com a UNIQUE.

FOREIGN KEY  – Esta Constraint determina que um valor inserido em uma Tabela obrigatoriamente tenha um referencia (exista) em outra Tabela. O uso desta constraint garante, por exemplo, que um produto que não esteja cadastrado na tabela de produtos seja associado a um fornecedor.

CHECK – Seu uso possibilita que seja determinado um valor para preenchimento de uma coluna. Por exemplo, uma coluna (SEXO) que deve receber a letra ‘F’ para Feminino e ‘M’ para Masculino. Ou então uma coluna (IDADE) seja preenchida apenas com valore maiores ou iguais a 18.

Criação de Tabela com Constraint.

 

As Constraints podem ser associadas a uma tabela em dois momentos, no momento da criação da tabela com o comando de definição CREATE TABLE ou pode ser adicionada a uma tabela já existente usando o comando de definição ALTER TABLE. A seguir a sintaxe para a criação das duas formas.

Sintaxe para a criação de uma tabela com uma Constraint:

 

CREATE TABLE <NOME_DA_TABELA>

(

               <COLUNA1> <TIPO>   

   

               CONSTRAINT <NOME_DA_CONSTRAINT>  <TIPO_DA_CONSTRAINT>

                (<COLUNA QUE RECEBERÁ a CONSTRAINT>)       

);

COMMIT;

 

Sintaxe Para a adição de uma Constraint a uma tabela já existente:

 

ALTER TABLE

<NOME_DA_TABELA>

ADD CONSTRAINT

<NOME_DA_CONSTRAINT>

<TIPO_DA_CONSTRAINT>;

COMMIT;

 

No Exemplo a seguir uma Constraint do tipo UNIQUE foi atribuída a coluna NOME_PRODUTO no momento da criação da Tabela no banco de Dados.

Exemplo 1:

CREATE TABLE TB_PRODUTO

 (

               COD_PRODUTO       NUMBER(38),

NOME_PRODUTO   VARCHAR2(40) ,

COD_CATEGORIA  NUMBER(38),

ORIGEM_PRODUTO CHAR(1),

              

                CONSTRAINT    UQ_NOME_PRODUTO_TB_PRODUTO

UNIQUE (NOME_PRODUTO)

);

COMMIT;

 

A seguir, uma constraint do tipo UNIQUE foi atribuída a coluna NOME_PRODUTO de uma tabela já existente no Banco de Dados.

Exemplo 2:

ALTER TABLE

TB_PRODUTO

ADD CONSTRAINT

PK_ COD_PRODUTO_TB_PRODUTO

PRIMARY  KEY  (COD_PRODUTO);

Commit;

 

No exemplo 2, pode-se observar que o nome da constraint começa com a sigla PK_, que é uma referencia ao seu tipo. Se a constraint fosse do tipo FOREIGN KEY seria usado FK_ como prefixo do nome.

A Constraint do tipo NOT NULL é associada a uma coluna na mesma linha em que este aparece no script de criação. Exemplo 3:

CREATE TABLE TB_FUNCIONARIO

(

               COD_FUNCIONARIO NUMBER(38) NOT NULL     

);

COMMIT;

 

O Exemplo 3, mostra a associação de uma Constraint do tipo NOT NULL a coluna COD_FUNCIONARIO, assim sempre que esta TABELA receber um novo registro, obrigará que um conteúdo seja inserido na coluna em que a Constraint foi associada.

 

Caso seja necessário adicionar uma Constraint NOT NULL a uma coluna de uma tabela já existente, aplica-se o script a seguir. Exemplo 4:

  ALTER TABLE

                              TB_PRODUTO

  MODIFY             NOME_PRODUTO           NOT NULL;

  COMMIT;

 

Observação:  As Constraints também podem ser adicionadas a uma VIEW. Veja mais informações sobre VIEWS no texto correspondente ao tema.

Caso for criada uma tabela para definir as categorias dos produtos (TB_CATEGORIA), a coluna COD_PRODUTO existente na TB_PRODUTOS deverá receber uma Constraint do tipo FOREIGN KEY (chave estrngeira). Esta Constraint irá garantir que nenhuma categoria que não esteja previamente cadastrada na TB_CATEGORIA possa ser associada a um produto, garantindo assim a integridade dos dados.

 

 

Antes de adicionar a Constraint na TB_PRODUTOS, primeiro a tabela TB_CATEGORIA deve ser criada. A seguir script para a criação da tabela, Exemplo 5:

CREATE TABLE TB_CATEGORIA

(

               COD_CATEGORIA NUMBER(38),

               CATEGORIA VARCHAR2(40),

 

CONSTRAINT PK_COD_CATEGORIA PRIMARY KEY(COD_CATEGORIA)        

);

COMMIT;

 

Com a tabela categoria criada, então é possivel a adição da Constraint de chave estrangeira na tabela TB_PRODUTO.

 

Sintaxe poara adição de Constraint de Chave Estrangeira (Foreign Key):

 

ALTER TABLE

NOME_TABELA_FILHA

ADD CONSTRAINT FK_NOME_CONSTRAINT FOREIGN KEY (COLUNA_FK)

REFERENCES NOME_TABELA_PAI (COLUNA_PK) INITIALLY DEFERRED;

 

O parametro INITIALLY DEFERRED, adia as restrições em tempo de execução, assim a validação é feita no commit. O parametro INITIALLY IMMEDIATE pode ser usado em seu lugar, assim garante-se que a validação será feita no momento da inclusão (Valor Padrão).

 

Segue código para a adição da Constrant de chave estrangeira na tabela TB_PRODUTO.

 

Exemplo 6:

 

ALTER TABLE

TB_PRODUTO

ADD CONSTRAINT

FK_TB_PRODUTO FOREIGN KEY (COD_CATEGORIA)

REFERENCES

TB_CATEGORIA (COD_CATEGORIA) INITIALLY DEFERRED;

               COMMIT;

 

Constraint CHECK

Quando existir a necessidade de pré definir valores a serem inseridos em um coluna, ou seja, deseja-se que apenas determinados valores possam ser inseridos, usa-se a constraint CHECK,

Exemplo de caso:

Deseja-se que na coluna ORIGEM_PRODUTO, seja preenchido com a letra I (de Importado) ou N indicando que o produto é Nacional.

Segue código para a adição da Constraint CHECK ao campo ORIGEM_PRODUTO da tabela TB_PRODUTO:

 

ALTER TABLE

TB_PRODUTO

ADD CONSTRAINT

                              CK_ORIGEM_PRODUTO CHECK (ORIGEM_PRODUTO in (‘I’, ’N’));

 

 

 Consultar Constraints de uma Tabela

 

Para verificar a existencia de Constrainsts associadas a uma Tabela e identificar quais são elas (Nome, tipo...)  basta fazer uma consulta na tabela USER_CONSTRAINTS, esta tabela é criada automaticamente pelo Oracle e possibilita consultar informações sobre todas as Constraints que foram criadas pelo usuário logado, chamamos este usuário de owner(proprietário). Script para a consulta:

 

Exemplo 7:

SELECT

               CONSTRAINT_NAME,

               CONSTRAINT_TYPE

FROM

               USER_CONSTRAINTS

WHERE

               TABLE_NAME = 'TB_PRODUTO’;

 

No script do Exemplo 7, foi realizada uma consulta na tabela USER_CONSTRAINTS que irá retornar os nomes e tipos das Constraints associadas à tabela TB_PRODUTO.  

 

Os valores para a coluna CONSTRAINT_TYPE são apresentados por uma letra indicativa do tipo:

• U: para UNIQUE;
• P: para Primary Key;
• R: para Foreign Key (Referencias);
• C: para Check e NOT NULL (Validações).

 

Caso o usuário logado possua privilégios de Administrador do Banco, outra tabela com informações sobre Constraints poderá ser consultada: ALL_CONSTRAINTS. Esta tabela contem informações de todas as Constraints criadas no Banco de Dados, independente do usuário que tenha criado, diferente da USER_CONSTRAINTS que exibe apenas as Constraints que pertencem ao usuário logado.

 

Exclusão de Constraints

 

Para a exclusão de uma Constraint é necessário saber o nome que esta foi criada, caso haja dúvida sobre o nome verifique a sessão deste texto que explica como realizar a consulta.

Sintaxe para a exclusão de uma Constraint:

 

ALTER TABLE

NOME_TABELA

DROP CONSTRAINT

NOME_CONSTRAINT ;

 

 

Exemplo 8:

 

ALTER TABLE

TB_PRODUTO

DROP CONSTRAINT

UQ_NOME_PRODUTO_TB_PRODUTO;

 

                              COMMIT;

 

No script do Exemplo 8, a Constraint UQ_NOME_PRODUTO_TB_PRODUTO foi excluída da tabela TB_PRODUTO. Esta Constraint foi adicionada a tabela no Exemplo 1 deste texto.

 

 

Exercício proposto - Constraint

1 – Crie um script para a criação de uma tabela chamada TB_MUSICA, que contenha colunas para armazenar os seguintes valores:

               Código da Música (chave primária);

               Nome da Gravadora (deve ser única);

               Internacional ou Nacional;          

Código da Gravadora.

 

2 – Elabore um script para adicionar uma regra na coluna que irá identificar se a música é Internacional ou Nacional, esta regra deve permitir que apenas as letras I (Importado) ou N (Nacional) sejam inseridas.

3 - Crie um script para a criação de uma tabela chamada TB_GRAVADORA, que contenha colunas para armazenar os seguintes valores:

               Código da Gravadora (chave primária);

               Nome da Gravadora (deve ser única).

 

4 – Relacione as tabelas TB_MUSICA e TB_GRAVADORA com uma Constraint.

5 – De forma Gráfica represente o relacionamento estabelecido no exercício 4.

6 – Em uma Tabela existe a necessidade que um campo seja Único e sempre deve ser preenchido. Qual Constraint deve-se associar a este campo?

 

 Referencia:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52180