[SQL Server] Exame 70-461 – Tópico 4: Criando e Modificando Constraints – Parte 01

Um importante passo, que faz parte do projeto de um banco de dados, é decidir a melhor maneira de como forçar e garantir a integridade dos dados. A integridade pode ser forçada completa e unicamente na camada de aplicação, ou exclusivamente no nível da camada de dados (leia-se banco de dados), ou, ainda, através de uma abordagem híbrida (esta é a abordagem mais comumente utilizada). Discussões e polêmicas à parte, existe um mínimo de “coisas” que você deveria implementar na camada de banco, visando garantir a integridade. Iremos falar sobre isso no decorrer deste post.

Tipos de Integridade de Dados

Conceitualmente existem três tipos de integridade de dados, conforme ilustra a figura abaixo.

· Integridade de domínio (coluna): especifica o conjunto de valores válidos para uma determinada coluna, bem como se a coluna aceita nulo ou não.

· Integridade de entidade (linha): requer que cada linha seja identificada de forma única na tabela, ou seja, cada linha possui uma identificação exclusiva. Isto é normalmente chamado de chave primária (primary key).

· Integridade referencial (entre tabelas): mantém um relacionamento consistente entre a Tabela A e a Tabela B. A chave primária da Tabela A existe como uma coluna na Tabela B, onde é conceitualmente chamada de chave-estrangeira. Desta forma, não será possível inserir um valor na coluna chave-estrangeira da Tabela B que não exista na coluna chave-primária da Tabela A. Por exemplo, não será possível inserir na tabela de Vendas o código de um cliente que não exista na tabela de Clientes.

Dentro do SQL Server temos as seguintes opções para forçar e garantir a integridade de dados:

Nulidade de Coluna (NULL / NOT NULL)

Este mecanismo permite forçar a integridade de domínio. Ele define se uma coluna é obrigatória. Existem muitas bases de dados legadas por aí cuja questão da nulidade fica completamente a cargo da aplicação, enquanto que nas tabelas todas as colunas estão setadas para aceitar nulo. Este tipo de design é um risco!

Vejamos como implementar a nulidade em tabelas utilizando as opções NULL e NOT NULL. Neste ponto estou destruindo e recriando o banco DBExame70461.

use master
GO

if db_id('DBExame70461') IS NOT NULL
BEGIN
    alter database DBExame70461 set single_user with rollback immediate;
    drop DATABASE DBExame70461;
END;

CREATE DATABASE DBExame70461;
GO

USE DBExame70461;
GO

CREATE TABLE dbo.Produto
(    Id INT PRIMARY KEY,
    Numero INT,
    Nome VARCHAR(50) NOT NULL,
    Descricao VARCHAR(500) NULL
);
INSERT dbo.Produto (Id, Nome) VALUES (1, 'Melancia');

Explicitamente defini a coluna NOME como obrigatória e a coluna DESCRICAO como opcional. E a coluna NUMERO, é obrigatória? Por padrão quando não explicitamos NULL ou NOT NULL a coluna será nulável. E a coluna ID? Neste caso, a coluna foi definida como chave-primária e, por consequência, ela será implicitamente criada como NOT NULL. Por fim, na instrução INSERT eu especifiquei apenas as colunas não nuláveis (obrigatórias).

Nulo não é valor, pelo contrário, nulo indica ausência de valor.

Constraints

Como já foi mencionado, constraint é um mecanismo que permite restringir o conjunto de valores para uma dada coluna ou, no nível da tabela, como os valores de diferentes colunas estão relacionados (relacionamento entre colunas da mesma tabela ou entre colunas de tabelas diferentes). Os tipos de constraints são:

DEFAULT Constraints

Este tipo de constraint fornece um valor padrão para uma coluna. Ter um valor padrão é um recurso útil nas instruções de INSERT. Vejamos abaixo.

USE DBExame70461;

DROP TABLE dbo.Produto;

CREATE TABLE dbo.Produto
(    Id INT PRIMARY KEY DEFAULT 1,
    Numero INT DEFAULT 77,
    Nome VARCHAR(50) NOT NULL DEFAULT 'Produto geral',
    Descricao VARCHAR(500) NULL DEFAULT 'Descrição geral'
);
GO

INSERT dbo.Produto (Id, Numero, Nome, Descricao)
VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT);

INSERT dbo.Produto (Id, Numero, Nome, Descricao)
VALUES (2, 88, 'Batata', DEFAULT);

INSERT dbo.Produto (Id)
VALUES (3);

Note que destruí e recriei a tabela. Para criar a constraint bastou adicionar a palavra reservada DEFAULT na instrução CREATE TABLE. No comando INSERT usei a palavra DEFAULT para que seja gravado o valor padrão na respectiva coluna. Na terceira instrução INSERT eu especifiquei apenas a coluna ID, omitindo as demais. Isso é válido quando as colunas omitidas são nuláveis ou possuem constraint default.

CHECK Constraints

Este tipo de constraint limita os valores que são permitidos em uma coluna. Vamos explorar algumas possibilidades.

CREATE TABLE dbo.Vendedores
(    Id INT NOT NULL PRIMARY KEY,
    Nome varchar(50) NOT NULL,
    Sexo char(1) NULL CHECK (Sexo in('M','F')) DEFAULT 'M',
    Comissao int NOT NULL
        constraint Ck_Vendedores_Comissao
        CHECK (Comissao BETWEEN 1 AND 5),
    TipoDeSalario varchar(10) NOT NULL
        CHECK (TipoDeSalario in ('Hora','Semana','Mes')),
    Telefone varchar(9) NULL CHECK (LEN(Telefone) >= 8)
);

Para criar a constraint bastou utilizar a palavra reservada CHECK na instrução CREATE TABLE. Na coluna COMISSAO, de forma explícita, informei o nome para a constraint: CK_Vendedores_Comissao. O benefício de criar uma constraint check com nome customizado é a facilidade em futuras alterações usando o comando ALTER TABLE. Note que para a coluna TELEFONE foi definida uma constraint que obriga o usuário a informar um número de telefone com pelo menos oito dígitos. Perceba, ainda, que para a coluna SEXO criei duas constraints: uma CHECK e outra DEFAULT. A figura abaixo mostra como ficou a tabela após a sua criação.

Vejamos o que acontece quando se tenta inserir, por exemplo, um registro de vendedor com uma comissão maior que 5% e com um número de telefone de 3 dígitos.

-- Tentando inserir registro com comissão acima de 5%
INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES (1, 'Antonio Silva', 'M', 7, 'Mes', 123456789);

-- Tentando inserir registro com telefone de 3 dígitos
INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES (1, 'Antonio Silva', 'M', 5, 'Mes', 123);

Agora, vamos falar de algo perigoso. Veja que definimos a coluna SEXO como nulável. Contudo, a constraint CHECK nessa coluna obriga informar apenas os valores M e F. Mas, o que acontecerá se tentarmos inserir um NULL nesta coluna? Devido ao fato da coluna aceitar NULL então a constraint não considera um NULL como falso, ou, em outras palavras, o NULL é simplesmente ignorado.

-- Inserindo com sucesso um NULL na coluna SEXO 
INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES (1, 'Antonio Silva', NULL, 1, 'Mes', 123456789);

Imaginemos um cenário em que tenhamos de importar um arquivo texto contendo um cadastro de vendedores. Ao abrir o arquivo texto notamos que foi informado valores de comissão fora da faixa de 1 a 5 (existem comissões zeradas e comissões maiores que 5). Para fazer a importação será necessário desabilitar a constraint na coluna COMISSAO e, após a importação, reabilitá-la.

-- Desabilitando a constraint check da coluna COMISSAO
ALTER TABLE dbo.Vendedores
    NOCHECK CONSTRAINT CK_Vendedores_Comissao;

-- Inserindo valores fora da faixa de 1 a 5
INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES 
(2, 'Jose', 'M', 7, 'Mes', 123456789),
(3, 'Pedro', 'M', 0, 'Mes', 123456789);

-- Habilitando a constraint check da coluna COMISSAO
ALTER TABLE dbo.Vendedores
    CHECK CONSTRAINT CK_Vendedores_Comissao;

Para eliminar uma constraint, independentemente do seu tipo (seja CHECK, DEFAULT, PRIMARY KEY, etc), faça conforme abaixo.

ALTER TABLE dbo.Vendedores
    DROP CONSTRAINT CK_Vendedores_Comissao;

Imaginemos agora o seguinte cenário (absurdo diga-se de passagem): alterar a tabela e adicionar uma constraint que não permita cadastrar, para novas inserções, nomes de vendedores contendo a palavra SILVA. Atente para o fato de que a nossa tabela de VENDEDORES já contém uma pessoa com o sobrenome SILVA. Veja:

Vamos tentar criar essa constraint e ver o que acontece.

-- Tentando criar uma constraint na coluna NOME
ALTER TABLE dbo.Vendedores
    ADD CONSTRAINT CK_Vendedores_Nome CHECK (Nome NOT LIKE '%Silva%');

O SQL Server retorna o seguinte erro:

Msg 547, Level 16, State 0, Line 1

The ALTER TABLE statement conflicted with the CHECK constraint “CK_Vendedores_Nome”. The conflict occurred in database “DBExame70461”, table “dbo.Vendedores”, column ‘Nome’.

Isso acontece porque no momento da criação da constraint o SQL Server verifica, por padrão, se existe algum valor na coluna que não atenda à regra especificada. Como o nosso cenário diz que essa regra deva ser aplicada apenas para “novas inserções”, vejamos como contornar tal situação.

ALTER TABLE dbo.Vendedores WITH NOCHECK
    ADD CONSTRAINT CK_Vendedores_Nome CHECK (Nome NOT LIKE '%Silva%');

Usamos a opção WITH NOCHECK, a qual instrui o SQL Server a criar a constraint sem fazer qualquer verificação. Desta forma, a constraint terá efeito somente para novas inserções. Exemplo:

INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES (4, 'Bruno Silva', 'M', 2, 'Mes', 123456789);

Table-Level CHECK Constraints

Além de checar valores para uma coluna em particular, uma constraint CHECK pode também ser aplicada no nível da tabela para checar valores relacionados em mais de uma coluna (desde que sejam da mesma tabela). Segue exemplo.

CREATE TABLE dbo.Ferias
(    Id INT NOT NULL PRIMARY KEY,
    Funcionario VARCHAR(50) NOT NULL,
    DataDeInicio DATETIME2 NOT NULL,
    DataDeFim DATETIME2 NOT NULL,
    CONSTRAINT CK_Ferias_Periodo CHECK (DataDeInicio < DataDeFim)
);

-- Tentando inserir uma Data de Início inválida
INSERT dbo.Ferias (Id, Funcionario, DataDeInicio, DataDeFim)
VALUES (1, 'Mariana Souza', '2012-10-31', '2012-10-30');

Veja que a constraint check força a integridade, garantindo que a data de início não seja igual ou maior que a data de fim.

Conclusão

No próximo post vamos finalizar o assunto de constraints, abordando os tipos primary key, unique key e foreign key. Até lá!