Atenção: esse artigo tem uma palestra complementar. Clique e assista!

Estamos de volta com a coluna Desafio SQL. Para quem nunca a leu, tratamos aqui de problemas enfrentados no dia-a-dia pelos profissionais que trabalham com bancos de dados. E para situarmos estes desafios, a cada artigo contamos um novo capítulo da história da empresa fictícia chamada ItsMyBusiness.

Por curiosidade, lembro aos interessados que esta história começou faz um bom tempo, na Revista #50. Este é o 14o capítulo desta "novela" (no bom sentido, claro).

A ItsMyBusiness é uma empresa de varejo que fez recentemente o seu site de e-commerce. E este site está "bombando"!

Vender mais significa mais dinheiro. Mas do ponto de vista de um banco de dados, representa também um volume maior de transações, maiores cuidados com performance, com armazenamento de dados e disponibilidade do sistema.

Estes são quesitos que devemos ter em mente desde o início da modelagem de qualquer banco. Mas o fato é que a ItsMyBusiness tratou seu e-commerce como se fosse uma experiência e não tomou cuidados básicos com a criação deste sistema.

Se você achou que este cenário se parece com o de algum sistema real com o qual você trabalhou, isso não é mera coincidência. É triste dizer, mas isso é terrivelmente comum. As empresas economizariam muito dinheiro se seguissem noções básicas de projeto.

Bom ou mal, certo ou errado, o fato é que agora a ItsMyBusiness tem que consertar o "motor do seu carro" quando a corrida já está em andamento. Uma série de melhorias e correções de bugs no modelo do banco de dados da empresa tem sido feitas nos últimos meses.

No nosso último desafio, apresentamos uma solução de modelagem para melhorar o controle sobre os pedidos que a ItsMyBusiness recebe. A solução previa o detalhamento dos possíveis status que um pedido poderia ter ao longo da sua história, ou seja, desde o momento em que ele é submetido pelo cliente até o momento em que ele é encerrado pela empresa (seja por qual razão for).

Esta mesma solução incluía a integridade referencial dos dados, ou seja, nosso modelo deveria garantir que os dados registrados no banco fossem 100% consistentes.

O modelo final da base, já incluídas as alterações citadas acima, é apresentado a seguir (Figura 1).

Figura 1. Modelo de dados simplificado da empresa ItsMyBusiness.

O script de criação deste banco de dados está disponível para download no portal da SQL Magazine. O script apresenta versões para rodar em SQL SERVER, DB2, ORACLE e FIREBIRD.

Voltando ao nosso assunto, para sorte da empresa ItsMyBusiness, o DBA que ela contratou, que no caso é você, é um cara muito cuidadoso.

Antes de implementar esta solução, o DBA abriu seu caderno de anotações e viu a seguinte frase escrita 100 vezes em letras garrafais:

“NUNCA FAREI ALTERAÇÕES NO MEU AMBIENTE DE PRODUÇÃO ANTES DE VALIDAR MINHAS SOLUÇÕES EM UM AMBIENTE DE TESTES QUE SIMULE A OPERAÇÃO REAL”.

Então ele passou o script de alteração da base para a equipe de testes, que depois de avaliar dezenas de casos de teste, apresentou o seguinte veredito:

“Por razões desconhecidas, o modelo em análise permite a inserção manual de informações inconsistentes na tabela tblPedidoStatus. O problema foi observado quando fizemos inserção de dados usando uma declaração SQL do tipo INSERT”.

Xiiii... a casa caiu!

Na verdade, ainda não caiu, porque a alteração não foi para produção e é para isso mesmo que fazemos testes meticulosos antes de qualquer implementação.

Já sabemos qual é o problema, pois os testadores não só disseram que o modelo “deu pau”. Eles disseram detalhadamente o que eles estavam fazendo quando o erro foi observado.

O que houve foi o seguinte: foram executadas várias declarações de inserção de dados na tabela dbo.tblPedidoHistorico. Algumas delas deveriam ser aceitas e outras deveriam ser rejeitadas. Chamamos isso de casos de testes.

Na Listagem 1 vemos quatro casos de teste que deveriam ser rejeitados.

Listagem 1. Os testes de rejeição .

 1    -- Inserção de código de pedido inexistente 
 2    --=====> Insert REJEITADO (CORRETO)
 3    INSERT INTO dbo.tblPedidoHistorico 
 4       (codPedido, codProduto, codPedidoStatus, Observacao) 
 5    VALUES (1000, 1,1, 'nao existe pedido # 1000')
 6    
 7    -- Inserção de código de produto inexistente 
 8    --=====> Insert REJEITADO (CORRETO)
 9    INSERT INTO dbo.tblPedidoHistorico 
 10      (codPedido, codProduto, codPedidoStatus, Observacao) 
 11   VALUES (1, 2000 ,1, 'nao existe produto # 2000')
 12   
 13   -- Insercao de código de status inexistente 
 14   --=====> Insert REJEITADO (CORRETO)
 15   INSERT INTO dbo.tblPedidoHistorico
 16      (codPedido, codProduto, codPedidoStatus, Observacao) 
 17   VALUES (1, 1, 3000, 'nao existe status # 3000')
 18   
 19   -- Insercao com produto que não pertence ao pedido
 20   --=====> Insert ACEITO (ERRADO!!!!!!!!!!!!!)
 21   INSERT INTO dbo.tblPedidoHistorico
 22      (codPedido, codProduto, codPedidoStatus, Observacao) 
 23   VALUES (1, 8, 1, 'o produto # 8 nao faz parte do pedido # 1')

Nos três primeiros, tentamos inserir códigos que não existem (linhas 1 a 22 da Listagem 1) e todos eles foram corretamente rejeitados.

Mas no quarto teste houve um erro. Neste teste, tínhamos códigos válidos para os campos codPedido, codProduto e codPedidoStatus. Mas o produto descrito não faz parte daquele pedido. O banco deveria rejeitar esta inserção, mas ele erradamente a aceitou (linhas 19 a 23).

Agora volta tudo para as suas mãos, já que você é o DBA/arquiteto/desenvolvedor responsável por este projeto. Sua missão é:

1. identificar onde está o problema

2. propor uma nova solução

Divirta-se!

Resposta do desafio

Muita gente simplesmente despreza o uso de chaves estrangeiras dentro dos seus bancos de dados. A maioria dos sistemas de gestão empresarial com os quais eu trabalhei as tratam como se fossem um pecado que deve ser evitado a qualquer custo.

A alegação é que as chaves estrangeiras tem impacto na performance do banco, porque o banco de dados sempre fará a validação dos dados contra cada uma das chaves estrangeiras existentes numa tabela toda vez que for executar qualquer declaração INSERT, DELETE ou UPDATE.

Isso é verdade. Existe mesmo um pequeno custo. E vai acontecer a cada transação que ocorrer no seu banco de dados, exigindo um pouco mais de tempo para execução de qualquer inserção, exclusão ou alteração nos seus dados.

Mas este pensamento estreito esquece um pequeno detalhe: a qualidade dos dados armazenados no seu banco. A integridade referencial (e todos os recursos que ela nos oferece, como é o caso das chaves estrangeiras) existe para garantir a consistência das informações.

Para uma empresa que vive na era da informação, é muito mais caro dispor de informações erradas e/ou inconsistentes do que levar um pouco mais de tempo para realizar cada transação.

Pessoalmente, eu uso chaves estrangeiras em todos os modelos de dados que eu crio e não vejo motivo que justifique a sua ausência.

Mas vamos ao que interessa.

Em primeiro lugar, temos que traduzir as palavras dos testadores em termos do modelo do banco de dados.

Quando dissemos "o produto descrito não faz parte daquele pedido", precisamos entender como o modelo lida com esta informação. Por isso vamos ver esta parte do modelo com maior detalhe (Figura 2).

Figura 2. Tratamento do ciclo de vendas.

Veja que o modelo usa a tabela dbo.tblPedidoDetalhe exatamente para armazenar as informações dos produtos que fazem parte de cada pedido. Tanto é assim que a chave primária desta tabela é composta pelos campos código de Pedido e código de Produto.

Entendendo isso, podemos reformular a frase que apresentamos acima. Em termos do modelo de dados, estamos falando que não existe na tabela dbo.tblPedidoDetalhe nenhuma chave primária composta pelos código de Pedido e código de Produto que estamos inserindo na tabela de histórico do status do pedido.

Para todos os efeitos práticos, nós acabamos de responder a primeira pergunta deste desafio!

Olhe novamente o modelo na Figura 2. Veja que a integridade referencial que criamos no último desafio não garante que a tabela dbo.tblPedidoHistorico receba combinações de códigos de pedido e de produto que já estejam cadastrados na tabela dbo.tblPedidoDetalhe.

Ao invés disso, a definição existente garante apenas que não poderemos cadastrar códigos de pedido e de produto que não existam nas tabelas dbo.tblPedido e dbo.tblProduto, respectivamente. Mas isso não faz tudo o que precisamos.

Escrevendo explicitamente a resposta à primeira pergunta: o modelo em teste não usa a integridade referencial adequada para a tabela dbo.tblPedidoHistorico, a qual precisa ser alterada.

Então tá, sabemos o que está errado. Mas o que vamos fazer para corrigir?

Bom, nós precisamos criar chaves estrangeiras na tabela dbo.tblPedidoHistorico que façam referência à chave primária da tabela dbo.tblPedidoDetalhe. E a chave primária é formada pelo par de campos codPedido + codProduto.

Maravilha. A solução parece simples. E aí vem outra pergunta: o que fazer com as chaves estrangeiras existentes?

Essa é uma boa pergunta. Muita gente acaba deixando “lixo” para trás dentro do banco de dados simplesmente porque ele parece “inofensivo”. Mas se as chaves existentes não resolvem o problema que deveriam cuidar, é muito importante avaliar se elas podem simplesmente ser eliminadas. Lembre-se que seria uma perda de tempo deixar para trás chaves estrangeiras inúteis, porque isso tem sim um pequeno impacto na performance do sistema, como eu já comentei anteriormente.

No caso em questão, basta olharmos para Figura 2 para termos uma resposta. A tabela dbo.tblPedidoHistorico possui três chaves estrangeiras: uma referenciando dbo.tblPedidoStatus, outra referenciando dbo.tblPedido e a terceira referenciando dbo.tblProduto.

A primeira delas, criada sobre o campo codPedidoStatus, não é afetada pela solução proposta. Portanto ela fica.

Já sobre as duas outras, veja que elas são idênticas às chaves estrangeiras que existem na tabela dbo.tblPedidoDetalhe: uma referenciando a tabela dbo.tblPedido e outra referenciando dbo.tblProduto.

Como nós vamos criar uma nova chave estrangeira em dbo.tblPedidoHistorico referenciando exatamente a tabela dbo.tblPedidoDetalhe, seria redundante manter as referências antigas. Então devemos excluir ambas.

Para isso, vamos precisar saber os nomes das chaves que serão excluídas. E esta parte nem sempre é tão fácil... E cada SGBD tem um meio de lhe mostrar esta informação.

No SQL SERVER, por exemplo, existem visões de sistema (as Dynamic Management Views ou DMVs) que nos dão estas e outras informações. Aos interessados, recomendo dar uma olhada na solução apresentada por Pinal Dave (http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-foreign-key-constraint-in-database/).

Respondemos metade da segunda pergunta. Dissemos o que fazer, mas não como fazer a alteração.

Faltou criarmos uma nova chave estrangeira referenciando dois campos ao mesmo tempo. O padrão ANSI SQL prevê esta situação de forma muito simples e intuitiva: basta referenciar os dois campos desejados, separando-os por uma vírgula.

A Listagem 2 mostra o script final incluindo a exclusão das chaves antigas e a criação da nova chave. Este script é válido para SQL SERVER, DB2 e ORACLE.

Listagem 2. Solução do desafio (SQL SERVER, DB2 e ORACLE).

 1    --exclui FKs existentes
 2    ALTER TABLE dbo.tblPedidoHistorico 
 3        DROP CONSTRAINT FK_tblPedidoH_tblPedido
 4    ;
 5
 6    ALTER TABLE dbo.tblPedidoHistorico 
 7        DROP CONSTRAINT FK_tblPedidoH_tblProduto
 8    ;
 9
 10   --cria a FK correta!!!
 11   ALTER TABLE dbo.tblPedidoHistorico 
 12       ADD CONSTRAINT fkPedidoH_DUPLO
 13         FOREIGN KEY (codPedido, codProduto)
 14         REFERENCES dbo.tblPedidoDetalhe(codPedido, codProduto)
 15   ;

Para o FIREBIRD, a única alteração necessária é excluir a referência ao esquema “dbo”, já que este SGBD não usa nome de esquema e/ou login à frente do nome dos objetos. O restante da sintaxe é idêntico, conforme Listagem 3.

Listagem 3. Solução do desafio (FIREBIRD).

 1    --exclui FKs existentes
 2    ALTER TABLE tblPedidoHistorico 
 3        DROP CONSTRAINT FK_tblPedidoH_tblPedido
 4    ;
 5
 6    ALTER TABLE tblPedidoHistorico 
 7        DROP CONSTRAINT FK_tblPedidoH_tblProduto
 8    ;
 9
 10   --cria a FK correta!!!
 11   ALTER TABLE tblPedidoHistorico 
 12       ADD CONSTRAINT fkPedidoH_DUPLO
 13         FOREIGN KEY (codPedido, codProduto)
 14         REFERENCES dbo.tblPedidoDetalhe(codPedido, codProduto)
 15   ;

Com isso terminamos o desafio SQL deste mês. Agora podemos passar a correção do código para nova série de testes e, se tudo der certo, em breve teremos as novas implementações rodando no ambiente de produção da ItsMyBusiness!

Espero que você tenha gostado.