Lição 2: Declarativa de Integridade de dados

maio 25, 2011

 

Validação de dados é uma das rotinas mais comum de um desenvolvedor.

Validação de dados

Há duas formas de validar a integridade dos dados, usando a integridade de dados declarativa ou integridade de dados procedural.

Integridade de dados declarativa é um conjunto de regras que são aplicadas a uma tabela e suas colunas utilizando o comando ALTER TABLE e CREATE TABLE. Essas regras são conhecidas como constraints.

A integridade de dados procedural é implementada através de procedures com validações de dados ou de triggers que checam os dados.

Como implementar a Integridade de dados

Há cinco tipos de constraints, PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE e DEFAULT.

PRIMARY KEY e UNIQUE constraints

Uma coluna ou uma combinação de colunas criam um identificador único para uma linha, que é reforçada pela criação de um índice único e não aceita duplicidade. Devido a isso primary key e unique constraints possuem a mesma limitação de tamanho como uma chave ou índice, ou seja, não pode haver mais de 16 colunas ou 900 bytes de dados.

Quando nada é especificado o índice criado é clustered e o índice para um unique é do tipo non-clustered. Você pode alterar esse comportamento especificando o tipo de index, veja o exemplo:

–Primary key as clustered index

ALTER TABLE MyTable

ADD PRIMARY KEY (MyTableId)

–Primary key as a nonclustered index

ALTER TABLE MyTable

ADD PRIMARY KEY NONCLUSTERED (MyTableId)

FOREIGN KEY constraints

 

FOREIGN KEY constraints

Foreign key constraint identifica uma coluna ou combinação de colunas que devem existir na mesma tabela ou em outra tabela. A foreign key constraint gerencia a integridade referencial entre duas tabelas ou apenas uma. Para implementar uma foreign key constraint você deve seguir essas regras:

– As colunas referenciadas devem ter o mesmo tipo de dados nos dois locais.

– As colunas referenciadas devem possuir um índice único.

Devido ao índice único, as colunas da foreign key devem respeitar a mesma limitação da primary key.

Quando ocorre uma violação de integridade referencial devido a um delete ou update a linha em questão retorna uma mensagem de erro e desfaz o comando que provocou o erro. Você pode definir qual ação o SQL Server deve realizar nesses casos, veja abaixo:

– NO ACTION – Comportamento padrão do SQL Server, desfaz a ação e retorna um erro.

– SET NULL – Altera os valores referenciados para NULL, não retorna erro.

– SET DEFAULT – Altera os valores referenciados para o valor default da coluna, não retorna erro.

– CASCADE – Exclui linhas referenciadas em um comando delete (ON DELETE) e atualiza linhas referenciadas em um comando update (ON UPDATE), não retorna erro.

Veja como implantar essas ações:

 

CREATE TABLE Customers (

        CustomerID INT PRIMARY KEY );

CREATE TABLE Orders (

        OrderID INT PRIMARY KEY

        ,CustomerID INT NULL

               REFERENCES Customers

                  ON DELETE SET NULL

                  ON UPDATE CASCADE );

 

CHECK CONSTRAINTS

Check constraints são uma série de regras que validam o dado antes de incluir na coluna. Veja algumas vantagens:

– São de fácil implementação.

– São verificadas automaticamente.

– Podem melhorar a performance.

Veja o script de uma simples check constraint que verifica se o preço do produto não é negativo:

ALTER TABLE Products

ADD CHECK (Price>=0.0);

Essa simplicidade é uma grande vantagem sobre as triggers, porém há algumas desvantagens:

– As mensagens de erro são geradas automaticamente e não podem ser substituida.

– Uma check constraint não pode “enxergar” o valor anterior da coluna.

Você pode enriquecer a check constraint utilizando funções de usuário, utilizando T-SQL ou ainda utilizar funções CLR (tanto em C# quanto em VB)

CHECK E FOREIGN KEY CONSTRAINTS VS. QUERY PERFORMANCE

Check e foreign key podem melhorar a performance, pois ambos são regras que o optimizer pode usá-los para criar planos de execução mais eficientes, o código abaixo é um simples exemplo de como isso acontece:

CREATE TABLE Customers (

                CustomerID INT PRIMARY KEY );

CREATE TABLE Orders (

               OrderID INT PRIMARY KEY

               ,CustomerID INT NOT NULL

                    CONSTRAINT FK_OrdersCustomers

                            REFERENCES Customers(CustomerID) );

Para entedermos melhor o que o SQL Server faz quando executa a consulta selecione a opçao Include Actual Execution Plan ou pressione Ctrl+M.

SELECT o.* FROM Orders AS o

WHERE

         EXISTS (SELECT * FROM Customers AS c

             WHERE c.CustomerID = o.CustomerID);

Perceba pelo plano de execução que a tabela Customers não foi acessada, apenas a tabela Orders, isto porque o optimizer sabe ela que não é necessaria para o operado EXISTS porque a foreign key constraint requer que todos os pedidos (orders) possuem referência com um cliente (customer), que é o que a cláusura where verifica.

 

clip_image001

 

Agora desabiliete a foreign key:

ALTER TABLE Orders NOCHECK CONSTRAINT FK_OrdersCustomers;

Execute a mesma consulta novamente e perceba que nesse caso o optimizer executa o operador EXISTS. Devido a foreign key constraint estar desabilitada o SQL Server não pôde ter certeza que todos os pedidos possuem clientes válidos. Em tabelas grandes isso pode fazer uma grande diferença.

clip_image002

Para habilitar novamente a utilize o seguinte código:

ALTER TABLE Orders CHECK CONSTRAINT FK_OrdersCustomers;

ALTER TABLE Orders

     WITH CHECK

       CHECK CONSTRAINT FK_OrdersCustomers

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com