Como usar restrições de chave estrangeira do MySQL

A restrição de chave estrangeira é usada para fazer um relacionamento entre duas tabelas no banco de dados MySQL. É uma característica muito importante do MySQL para definir diferentes tipos de restrições nas tabelas relacionadas. Para definir qualquer restrição de chave estrangeira para qualquer tabela, você deve usar a chave primária de outra tabela. A chave primária é uma chave única para uma tabela identificar a linha específica da tabela e quando essa chave primária é usada em outra tabela para fazer um relacionamento um para um ou um para muitos ou muitos para muitos é chamado de chave estrangeira. As características das chaves estrangeiras e como essas chaves podem ser usadas nas tabelas MySQL são mostradas neste artigo.

Recursos de restrições de chave estrangeira:

Algumas características importantes da restrição de chave estrangeira são explicadas abaixo.

• O tipo de dados da chave estranha usada na tabela filho deve ser a mesma com o tipo de dados da chave primária usada na tabela pai para consultar a chave estrangeira.
• Qualquer coluna de índice ou várias colunas pode ser referenciada como uma chave estrangeira apenas para a tabela Innodb.
• Referências Privilégios ou pelo menos um dos privilégios das declarações selecionadas, inseridas, atualizadas e excluídas são necessárias para criar uma chave estrangeira.
• Uma chave estrangeira pode ser criada de duas maneiras. Um usando a instrução CREATE e outra usando a instrução ALTER.

Pré -requisito:

Antes de criar uma restrição de chave estrangeira, você deve criar um banco de dados e uma tabela pai com a chave primária. Suponha que o nome do banco de dados seja 'biblioteca'e contém duas tabelas de pais denominadas'livros' e 'mutuário'. Faça uma conexão com o MySQL Server usando o mysql cliente e execute as seguintes instruções SQL para criar o banco de dados e as tabelas.

Criar biblioteca de banco de dados;
Use biblioteca;
Crie livros de mesa (
id int não nulo auto_incrent,
Título Varchar (50) não nulo,
Autor Varchar (50) não nulo,
Editor Varchar (50) não nulo,
Chave primária (id)
) Mecanismo = innodb;
Crie mutuários de tabela (
id varchar (50) não nulo,
Nome Varchar (50) não nulo,
endereço Varchar (50) não nulo,
Email Varchar (50) não nulo,
Chave primária (id)
) Mecanismo = innodb;

Defina a restrição de chave estrangeira usando a declaração de criação

Crie uma tabela chamada 'book_borrow_info'Com restrições de chave estrangeira executando a seguinte declaração. Aqui o book_id o campo é a Chave estrangeira para esta tabela e todo valor desse campo deve existir no eu ia Campo de livros mesa. livros é a tabela pai e book_borrow_info é a mesa infantil. Duas restrições também são definidas com a chave estrangeira aqui. Estes são Excluir cascata e Atualize Cascade. Isso significa que, se alguma chave primária remover ou atualizar da tabela pai, os registros correspondentes relacionados à tabela infantil relacionados à chave estrangeira serão removidos ou a chave estrangeira será atualizada.

Criar tabela book_borrow_info (
Borrow_id Varchar (50),
book_id int,
DATA AMBOLTE_DATE NÃO NULL,
Return_date Data não nula,
status varchar (15) não nulo,
Índice par_ind (book_id),
Chave primária (Borrow_id, Borrow_date),
Chave estrangeira (book_id) Referências Livros (ID)
Em excluir cascata
Na atualização Cascade
) Mecanismo = innodb;

Agora, execute as seguintes declarações SQL para inserir alguns registros em ambas as tabelas. A primeira declaração de inserção inserirá quatro registros em livros mesa. Os quatro valores de eu ia Campo de livros A tabela será 1, 2, 3 e 4 para o atributo de incremento automático. A segunda declaração de inserção inserirá quatro registros em book_borrow_info com base no eu ia valor de livros mesa.

Insira os valores dos livros
(Null, 'Kill a Mockingbird', 'Harper Lee', 'Grand Central Publishing'),
(Null, 'Cem Anos de Solitude', 'Garcia Marquez', 'Lutfi Ozkok'),
(Nulo, 'uma passagem para a Índia', 'Forster, e.M.',' BBC Hulton Picture Library '),
(Null, 'Invisible Man', 'Ralph Ellison', 'Encyclopædia Britannica, Inc.');
Inserir em book_borrow_info valores
('123490', 1, '2020-02-15', '2020-02-25', 'retornou'),
('157643', 2, '2020-03-31', '2020-03-10', 'pendente'),
('174562', 4, '2020-04-04', '2020-04-24', 'emprestado'),
('146788', 3, '2020-04-10', '2020-01-20', 'emprestado');

Se você tentar inserir um valor no campo de chave estrangeira da tabela infantil que não existe no campo chave primário da tabela pai, o MySQL gerará um erro. A seguinte instrução SQL gerará um erro porque a tabela pai, livros não contém nenhum valor de identificação 10.

Inserir em book_borrow_info valores
('195684', 10, '2020-04-15', '2020-04-30', 'retornou');

Depois de executar a seguinte declaração de exclusão, quando o quarto recorde será removido do livros tabela então os registros relacionados do book_borrow_info A tabela será removida automaticamente para a restrição de chave estrangeira.

Exclua dos livros onde id = 4;
Selecione * de livros;
Selecione * de book_borrow_info;

Defina a restrição de chave estrangeira usando a declaração alterada

A princípio, insira alguns registros em mutuários Tabela e esta tabela serão definidas como tabela pai no próximo ALTERAR declaração.

Insira os valores dos mutuários
('123490', 'Patrick Wood', '34 West Street Lancaster la14 9zh ',' [email protected] '),
('157643', 'Ezra Martin', '10 The Grove Birmingham B98 1eu ',' [email protected] '),
('174562', 'John Innes Archie', '55 Main Road Liverpool L2 3OD ',' [email protected] '),
('146788', 'Frederick Hanson', '85 Highfield Road Shrewsbury Sy46 3me ',' [email protected] ');

Execute o seguinte ALTERAR declaração para definir outra restrição de chave estrangeira para book_borrow_info tabela para fazer o relacionamento com mutuários mesa. Aqui, emprestado_id é definido como uma chave estrangeira para book_borrow_info mesa.

ALTER TABLE BOOK_BORROW_INFO Adicionar restrição FK_BRORER
Chave estrangeira (Borrow_Id) Referências Matores (ID) na Cascade Excluir na atualização RESTRITION;

Agora, insira um recorde em book_borrow_info com válido emprestado_id valor que existe em eu ia Campo de mutuários mesa. 157643 O valor existe na tabela de mutuários e a seguinte declaração de inserção será executada com sucesso.

Inserir em book_borrow_info valores
('157643', 1, '2020-03-10', '2020-03-20', 'retornou');

A instrução Inserir a seguir gerará uma mensagem de erro porque o valor do ID 195680 não existe na tabela de mutuários.

Inserir em book_borrow_info valores
('195680', 1, '2020-04-15', '2020-04-30', 'retornou');

Conclusão:

Definir restrições de chave estrangeira corretamente é uma tarefa muito importante para criar um banco de dados relacional e gerenciar dados entre as tabelas adequadamente. Conhecer os usos de restrições de chave estrangeira é muito essencial para os designers de banco de dados. Espero que este artigo ajude os novos designers de banco de dados a entender o conceito de restrições de chave estrangeira e a aplicá -las corretamente em suas tarefas.