Chaves estrangeiras e regras da aplicação

Uma venda aponta para um cliente, e cada item de venda aponta para a venda a que pertence e para um produto. Quando um cadastro é apagado, as linhas que apontavam para ele podem ficar sem par. A proteção dessas ligações pode estar no próprio banco, em uma regra declarada na tabela, ou apenas na aplicação, e essa diferença decide o que acontece quando alguém altera os dados direto no banco.

A chave estrangeira

A chave estrangeira, chamada em inglês de foreign key, é uma regra declarada na tabela que liga uma coluna à chave de outra tabela, normalmente a chave primária. A tabela que aponta é chamada de tabela filha, e a tabela apontada é chamada de tabela pai. Na ligação entre os itens e os produtos, a tabela filha é itens_venda, e a coluna produto_id aponta para a coluna id da tabela pai, produtos.

Com a chave estrangeira declarada, o MySQL confere as gravações na tabela filha. A inclusão de um item com um produto_id que não existe na tabela produtos é recusada com o erro 1452, de linha filha sem a linha pai correspondente.

O comando SHOW CREATE TABLE itens_venda; mostra as chaves estrangeiras declaradas, nas linhas que contêm as palavras FOREIGN KEY e REFERENCES. Entre os engines mais comuns, as chaves estrangeiras funcionam apenas no InnoDB. O engine MyISAM aceita a declaração no comando que cria a tabela, mas não a guarda nem a aplica.

O que acontece quando a linha pai é apagada

A chave estrangeira define, na cláusula ON DELETE, o que acontece com as linhas filhas quando a linha pai é apagada:

• RESTRICT ou NO ACTION: a exclusão da linha pai é recusada com o erro 1451, de linha pai ainda referenciada. É o comportamento padrão quando a cláusula não é informada.
• CASCADE: a exclusão da linha pai apaga também as linhas filhas. A exclusão de uma única venda apaga todos os itens dessa venda.
• SET NULL: a exclusão da linha pai deixa em NULL a coluna de ligação das linhas filhas.

Uma exclusão em cascata pode apagar muitas linhas em outras tabelas, além da linha indicada no comando. A regra SET NULL também merece atenção no cenário. A coluna cliente_id da tabela vendas fica em NULL quando o consumidor não se identifica. Se a ligação entre vendas e clientes usasse SET NULL, as vendas de um cliente apagado passariam a parecer vendas de consumidores não identificados.

Quando as regras ficam na aplicação

Muitos sistemas não declaram chaves estrangeiras e mantêm as ligações apenas no próprio código. Antes de gravar um item de venda, a aplicação confere se o produto existe, e antes de excluir um cliente, confere se ele tem vendas. Esse arranjo é comum em sistemas que nasceram em bancos de dados sem esse recurso e em sistemas que recebem dados de outras origens, como a matriz, em uma ordem que nem sempre respeita as ligações. No cenário, o banco loja segue esse arranjo, e nenhuma das ligações entre vendas, itens, clientes e produtos tem chave estrangeira declarada.

Nesse arranjo, o banco aceita qualquer valor na coluna de ligação. Um comando feito direto no banco não passa pelas conferências da aplicação, e uma exclusão que a tela recusaria é aceita pelo MySQL sem nenhum aviso. A falta da chave estrangeira não retira as outras proteções do banco, como as permissões do usuário e as regras de valor único, mas deixa aquela ligação sem proteção no banco.

Descobrir onde está a regra

Dois comandos mostram se o banco protege as ligações:

• SHOW CREATE TABLE itens_venda;: mostra as chaves estrangeiras de uma tabela;
• SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'loja' AND REFERENCED_TABLE_NAME IS NOT NULL;: lista todas as chaves estrangeiras do banco loja.

O information_schema é um banco especial do MySQL que descreve os outros bancos, com as tabelas, as colunas e as regras de cada um. Quando a consulta não devolve nenhuma linha, como no banco loja, as ligações existem apenas na aplicação.

Os registros órfãos

Um registro órfão é uma linha que aponta para outra que não existe mais. A tabela pode estar fisicamente íntegra e, ainda assim, conter registros órfãos, porque o problema está na relação entre as tabelas, e não nos arquivos. A junção à esquerda encontra esses registros:

• SELECT i.id, i.venda_id FROM itens_venda i LEFT JOIN vendas v ON v.id = i.venda_id WHERE v.id IS NULL;: mostra os itens cuja venda não existe;
• SELECT v.id, v.cliente_id FROM vendas v LEFT JOIN clientes c ON c.id = v.cliente_id WHERE v.cliente_id IS NOT NULL AND c.id IS NULL;: mostra as vendas que apontam para clientes inexistentes.

A segunda consulta deixa de fora as vendas com cliente_id em NULL, porque essas vendas são de consumidores não identificados, e não registros órfãos.

Uma venda cujo cliente foi apagado desaparece de um relatório que junta vendas e clientes com a junção comum, e a tela que abre essa venda pode falhar ao procurar o cliente. A correção depende da regra de negócio: recuperar a linha pai de uma cópia, ligar a linha filha a outro cadastro ou excluí-la pela aplicação. Apagar os órfãos direto no banco pode apagar o histórico de vendas.

A conferência desligada

A variável foreign_key_checks liga e desliga a conferência das chaves estrangeiras na conexão:

• SELECT @@foreign_key_checks;: mostra o valor 1 quando a conferência está ligada e o valor 0 quando está desligada.

As ferramentas de cópia e de restauração costumam desligar essa conferência durante a carga, para que as tabelas possam ser carregadas em qualquer ordem. Quando a conferência é religada, o MySQL não confere as linhas gravadas no intervalo. Por isso, a restauração de uma única tabela, a partir de uma cópia mais antiga, pode deixar linhas filhas sem a linha pai, sem nenhum erro durante a carga.