Qualquer pessoa que tenha desenvolvido uma pequena aplicação web baseada em banco de dados usando MySQL sabe que criar, recuperar, atualizar e excluir tabelas em um banco de dados relacional são processos relativamente simples. Teoricamente, contanto que você domine o uso das instruções SQL mais comuns e esteja familiarizado com a linguagem de script do lado do servidor que você escolhe usar, é suficiente lidar com as várias operações necessárias nas tabelas MySQL, especialmente quando você usa o rápido MyISAM. mecanismo de banco de dados quando. Mas mesmo nos casos mais simples, as coisas são mais complicadas do que pensamos. Abaixo usamos um exemplo típico para ilustrar. Suponha que você tenha um blog que atualiza quase todos os dias e que o site permite que os visitantes comentem suas postagens.
Nesse caso, nosso esquema de banco de dados deve incluir pelo menos duas tabelas MyISAM, uma para armazenar as postagens do seu blog e outra para lidar com os comentários dos visitantes. Obviamente, existe um relacionamento um-para-muitos entre essas duas tabelas, portanto, precisamos definir uma chave estrangeira na segunda tabela para que a integridade do banco de dados possa ser mantida quando as linhas de dados forem atualizadas ou excluídas.
Para uma aplicação como a acima, manter a integridade das duas tabelas não é apenas um sério desafio, mas a maior dificuldade é que temos que manter sua integridade no nível da aplicação. Esta é a abordagem adotada durante o desenvolvimento para a maioria dos projetos web que não requerem o uso de transações porque as tabelas MyISAM oferecem excelente desempenho.
É claro que isso também tem um custo. Como disse anteriormente, a aplicação deve manter a integridade e a consistência do banco de dados, o que significa implementar uma lógica de programação mais complexa para lidar com os relacionamentos entre as diversas tabelas. Embora o acesso ao banco de dados possa ser simplificado através do uso de camadas de abstração e módulos ORM, à medida que o número de tabelas de dados exigidas por uma aplicação aumenta, a lógica necessária para lidar com elas sem dúvida se tornará mais complexa.
Então, para o MySQL, existe algum método de processamento de chave estrangeira no nível do banco de dados para ajudar a manter a integridade do banco de dados? Felizmente, a resposta é sim! Este recurso nos permite acionar determinadas ações, como atualizar e excluir determinadas linhas de dados da tabela para manter relacionamentos predefinidos.
Tudo tem prós e contras, e a principal desvantagem de usar tabelas InnoDB é que elas são mais lentas que MyISAM, especialmente em aplicações de grande escala onde muitas tabelas devem ser consultadas. Felizmente, a tabela MyISAM na versão mais recente do MySQL também suporta restrições de chave estrangeira.
Este artigo apresentará como aplicar restrições de chave estrangeira às tabelas do InnoDB. Além disso, usaremos uma classe abstrata MySQL simples baseada em PHP para criar o código de exemplo relevante, é claro, você também pode usar sua outra linguagem de servidor favorita; Agora, começamos a apresentar como aplicar restrições de chave estrangeira ao MySQL.
Quando usar restrições de chave estrangeira
Para ser honesto, ao usar tabelas InnoDB no MySQL, você não precisa necessariamente usar restrições de chave estrangeira. No entanto, para fins de restrições de chave estrangeira em certas situações, usaremos o código do exemplo mencionado anteriormente para explicar em detalhes. Inclui duas tabelas MyISAM, usadas para armazenar postagens e comentários de blogs.
Ao definir o esquema do banco de dados, precisamos estabelecer um relacionamento um-para-muitos entre as duas tabelas, criando uma chave estrangeira na tabela onde os comentários são armazenados para mapear as linhas de dados (ou seja, comentários) para um artigo de blog específico. Aqui está o código SQL básico para criar uma tabela MyISAM de exemplo:
DROP TABLE SE EXISTE `test`.`blogs`;
CRIAR TABELA `test`.`blogs` (
`id` INT (10) AUTO_INCREMENT NÃO ASSINADO,
`título` TEXTO,
`conteúdo` TEXTO,
`autor` VARCHAR(45) DEFAULT NULL,
CHAVE PRIROSA (`id`)
) MOTOR=MyISAM DEFAULT CHARSET=utf8;
DROP TABLE SE EXISTE `test`.`comments`;
CRIAR TABELA `test`.`comments` (
`id` INT (10) AUTO_INCREMENT NÃO ASSINADO,
`blog_id` INT (10) NÃO ASSINADO PADRÃO NULO,
`comentário` TEXTO,
`autor` VARCHAR(45) DEFAULT NULL,
CHAVE PRIROSA (`id`)
) MOTOR=MyISAM DEFAULT CHARSET=utf8;
Acima, acabamos de definir duas tabelas MyISAM, que formam a camada de dados da aplicação de blog. Como você pode ver, a primeira tabela é chamada de blogs. Ela consiste em alguns campos óbvios, que são usados para armazenar o ID, o título e o conteúdo de cada postagem do blog e, finalmente, o autor. A segunda tabela é chamada de comentários, que é usada para armazenar comentários relacionados a cada postagem do blog. Ela usa o ID da postagem do blog como chave estrangeira para estabelecer um relacionamento um-para-muitos.
Até agora, nosso trabalho tem sido fácil porque criamos apenas duas tabelas MyISAM simples. A seguir, o que queremos fazer é preencher essas tabelas com alguns registros para demonstrar melhor o que deve ser feito na outra tabela quando uma entrada for excluída na primeira tabela.
Atualizar e manter a integridade do banco de dados
Na parte anterior, criamos duas tabelas MyISAM para servir como camada de dados da aplicação de blog. É claro que a introdução acima ainda é muito simples e precisamos discuti-la mais detalhadamente. Para fazer isso, preencheremos essas tabelas com alguns registros usando comandos SQL da seguinte forma:
INSERT INTO blogs (id, título, conteúdo, autor) VALUES (NULL,'Título da primeira entrada do blog', 'Conteúdo da primeira entrada do blog', 'Ian')
INSERT INTO comentários (id, blog_id, comentário, autor) VALUES (NULL, 1, 'Comentando a primeira entrada do blog', 'Susan Norton'), (NULL, 1, 'Comentando a primeira entrada do blog', 'Rose Wilson')
O código acima simula a situação em que as leitoras Susan e Rose comentaram em nosso primeiro blog. Suponha que agora queiramos atualizar o primeiro blog com outra postagem. Claro, esta situação é possível.
Neste caso, para manter a consistência do banco de dados, a tabela de comentários também deve ser atualizada adequadamente, seja manualmente ou por um aplicativo que processe a camada de dados. Neste exemplo, usaremos um comando SQL para concluir a atualização da seguinte forma:
UPDATE blogs SET id = 2, title = 'Título da primeira entrada do blog', content = 'Conteúdo da primeira entrada do blog', autor = 'John Doe' WHERE id = 1
ATUALIZAR comentários SET blog_id = 2 WHERE blod_id = 1
Conforme mencionado anteriormente, como o conteúdo do item de dados do primeiro blog foi atualizado, a tabela de comentários também deve refletir essa alteração. É claro que, na realidade, esta operação de atualização deve ser concluída na camada de aplicação e não manualmente, o que significa que esta lógica deve ser implementada usando uma linguagem do lado do servidor.
Para completar esta operação, o PHP pode usar um subprocesso simples, mas na verdade, se forem usadas restrições de chave estrangeira, a operação de atualização da tabela de comentários pode ser delegada ao banco de dados.
Conforme mencionado anteriormente neste artigo, as tabelas InnoDB MySQL fornecem suporte perfeito para esta funcionalidade. Portanto, na parte posterior usaremos restrições de chave estrangeira para recriar o código do exemplo anterior.
Atualizações em cascata no banco de dados
Abaixo, reestruturaremos o código do exemplo anterior usando restrições de chave estrangeira e uma tabela InnoDB (em vez do tipo MyISAM padrão). Para fazer isso, primeiro redefina as duas tabelas de amostra para que possam usar um mecanismo de banco de dados específico. Para fazer isso, você pode usar o código SQL como o seguinte:
DROP TABLE SE EXISTE `test`.`blogs`;
CRIAR TABELA `test`.`blogs` (
`id` INT (10) AUTO_INCREMENT NÃO ASSINADO,
`título` TEXTO,
`conteúdo` TEXTO,
`autor` VARCHAR(45) DEFAULT NULL,
CHAVE PRIROSA (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE SE EXISTE `test`.`comments`;
CRIAR TABELA `test`.`comments` (
`id` INT (10) AUTO_INCREMENT NÃO ASSINADO,
`blog_id` INT (10) NÃO ASSINADO PADRÃO NULO,
`comentário` TEXTO,
`autor` VARCHAR(45) DEFAULT NULL,
CHAVE PRIROSA (`id`),
CHAVE `blog_ind` (`blog_id`),
RESTRIÇÃO `comments_ibfk_1` CHAVE ESTRANGEIRA (`blog_id`) REFERÊNCIAS `blogs` (`id`) NA CASCADE DE ATUALIZAÇÃO
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Uma diferença óbvia entre o código aqui e o código anterior é que as duas tabelas agora usam o mecanismo de armazenamento InnoDB, para que possam suportar restrições de chave estrangeira. Além disso, também precisamos ficar atentos ao código que define a tabela de comentários:
RESTRIÇÃO `comments_ibfk_1` CHAVE ESTRANGEIRA (`blog_id`) REFERÊNCIAS `blogs` (`id`) NA CASCADE DE ATUALIZAÇÃO
Na verdade, esta instrução notifica o MySQL que quando a tabela blogs for atualizada, o valor da chave estrangeira blog_id na tabela de comentários também deverá ser atualizado. Em outras palavras, o que é feito aqui é deixar o MySQL manter a integridade do banco de dados em cascata. Isso significa que quando um blog é atualizado, os comentários conectados a ele também devem refletir imediatamente esta mudança. . Isso não é feito na camada de aplicação.
As duas tabelas de exemplo do MySQL foram definidas. Agora, atualizar essas duas tabelas é tão simples quanto executar uma instrução UPDATE, conforme mostrado abaixo:
"ATUALIZAR blogs SET id = 2, title = 'Título da primeira entrada do blog', content = 'Conteúdo da primeira entrada do blog', autor = 'John Doe' WHERE id = 1"
Conforme mencionado anteriormente, não precisamos atualizar a tabela de comentários porque o MySQL cuidará disso automaticamente. Além disso, você pode fazer com que o MySQL não faça nada ao tentar atualizar uma linha na tabela de blogs removendo a parte "ON UPDATE" da consulta ou especificando "NO ACTION" e "RESTRICT". Claro, você também pode deixar o MySQL fazer outras coisas, que serão apresentadas em artigos subsequentes.
Através da introdução acima, acho que todos têm uma compreensão clara de como usar restrições de chave estrangeira em conjunto com tabelas InnoDB no MySQL. É claro que você também pode escrever código imediato para aprofundar ainda mais sua compreensão dessa conveniente função de banco de dados.