Recursos de restrições de chave estrangeira:
Alguns recursos importantes da restrição de chave estrangeira são explicados a seguir.
- O tipo de dados da chave estrangeira usado na tabela filho deve ser o mesmo que o tipo de dados da chave primária usado na tabela pai para se referir à chave estrangeira.
- Qualquer coluna de índice ou colunas múltiplas podem ser referenciadas como uma chave estrangeira apenas para a tabela InnoDB.
- Os privilégios de referências ou pelo menos um dos privilégios das instruções SELECT, INSERT, UPDATE e DELETE são necessários para criar uma chave estrangeira.
- Uma chave estrangeira pode ser criada de duas maneiras. Um usando a instrução CREATE e outro 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 pai chamadas' livros ' e ' mutuário ’. Faça uma conexão com o servidor MySQL usando o mysql cliente e execute as seguintes instruções SQL para criar o banco de dados e as tabelas.
CRIAR BASE DE DADOS biblioteca;
USAR biblioteca;
CRIAR TABELA livros(
Eu iria INT NÃO NULO INCREMENTO AUTOMÁTICO ,
título varchar (cinquenta) NÃO NULO ,
autor varchar (cinquenta) NÃO NULO ,
editor varchar (cinquenta) NÃO NULO ,
CHAVE PRIMÁRIA (Eu iria)
) MOTOR = INNODB ;
CRIAR TABELA mutuários(
Eu iria VARCHAR (cinquenta) NÃO NULO ,
nome varchar (cinquenta) NÃO NULO ,
Morada varchar (cinquenta) NÃO NULO ,
o email varchar (cinquenta) NÃO NULO ,
CHAVE PRIMÁRIA (Eu iria)
) MOTOR = INNODB ;
Defina a restrição de chave estrangeira usando a instrução CREATE
Crie uma tabela chamada ‘ book_borrow_info ‘Com restrições de chave estrangeira executando a seguinte instrução. Aqui o book_id campo é um chave estrangeira para esta tabela e todos os valores deste campo devem existir no Eu iria Campo de livros tabela. livros é a tabela pai e book_borrow_info é a tabela filho. Duas restrições também são definidas com a chave estrangeira aqui. Estes são DELETE CASCADE e ATUALIZAR CASCADE . Isso significa que se qualquer chave primária for removida ou atualizada da tabela pai, os registros correspondentes relacionados à tabela filho relacionada à chave estrangeira serão removidos ou a chave estrangeira será atualizada.
CRIAR TABELA book_borrow_info(
borrow_id VARCHAR (cinquenta),
book_id INT ,
borrow_date ENCONTRO NÃO NULO ,
data de retorno ENCONTRO NÃO NULO ,
status VARCHAR (quinze) NÃO NULO ,
ÍNDICE par_ind(book_id),
CHAVE PRIMÁRIA (borrow_id,borrow_date),
CHAVE ESTRANGEIRA (book_id) REFERÊNCIAS livros(Eu iria)
SOBRE EXCLUIR CASCATA
SOBRE ATUALIZAR CASCATA
) MOTOR = INNODB ;
Agora, execute as seguintes instruções SQL para inserir alguns registros em ambas as tabelas. A primeira instrução INSERT irá inserir quatro registros em livros tabela. Os quatro valores de Eu iria Campo de livros a tabela será 1, 2, 3 e 4 para o atributo de incremento automático. A segunda instrução INSERT irá inserir quatro registros em book_borrow_info baseado no Eu iria valor de livros tabela.
INSERIR EM livros VALORES
( NULO , 'Matar a esperança', 'Harper Lee', 'Grand Central Publishing'),
( NULO , 'Cem anos de Solidão', 'Garcia Marquez', 'Lutfi Ozkok'),
( NULO , 'A Passage to India', 'Forster, E.M.', 'BBC Hulton Picture Library'),
( NULO , 'Homem invisível', '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 filho que não existe no campo de chave primária da tabela pai, o MySQL irá gerar um erro. A seguinte instrução SQL irá gerar um erro porque a tabela pai, livros não contém nenhum valor de id 10 .
INSERIR EM book_borrow_info VALORES('195684', 10, '2020-04-15', '2020-04-30', 'Retornou');
Depois de executar a seguinte instrução DELETE, quando o quarto registro será removido do livros tabela, em seguida, os registros relacionados da book_borrow_info a tabela será removida automaticamente para a restrição de chave estrangeira.
EXCLUIR A PARTIR DE livros ONDE Eu iria= 4;SELECIONE * a partir de livros;
SELECIONE * a partir de book_borrow_info;
Defina a restrição de chave estrangeira usando a instrução ALTER
Em primeiro lugar, insira alguns registros em mutuários tabela e esta tabela será definida como tabela pai no próximo ERA demonstração.
INSERIR EM mutuários VALORES('123490', 'Patrick Wood', '34 West Street LANCASTER LA14 9ZH ', '[email protegido]'),
('157643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU ', '[email protegido]'),
('174562', 'John Innes Archie', '55 Main Road LIVERPOOL L2 3OD ', '[email protegido]'),
('146788', 'Frederick Hanson', '85 Highfield Road SHREWSBURY SY46 3ME ', '[email protegido]');
Execute o seguinte ERA declaração para definir outra restrição de chave estrangeira para book_borrow_info mesa para fazer o relacionamento com mutuários tabela. Aqui, borrow_id é definido como uma chave estrangeira para book_borrow_info tabela.
ALTER TABLE book_borrow_info ADD CONSTRAINT fk_borrowerCHAVE ESTRANGEIRA(borrow_id)REFERÊNCIAS mutuários(Eu iria)ON DELETE CASCADE ON UPDATE RESTRICT;
Agora, insira um registro em book_borrow_info com válido borrow_id valor que existe em Eu iria Campo de mutuários tabela. 157643 o valor existe na tabela de devedores e a seguinte instrução INSERT será executada com sucesso.
INSERIR EM book_borrow_info VALORES('157643', 1, '2020-03-10', '2020-03-20', 'Retornou');
A seguinte instrução INSERT irá gerar uma mensagem de erro porque o valor do id 195680 não existe na tabela de devedores.
INSERIR EM book_borrow_info VALORES('195680', 1, '2020-04-15', '2020-04-30', 'Retornou');
Conclusão:
Definir as restrições de chave estrangeira de maneira adequada é uma tarefa muito importante para criar um banco de dados relacional e gerenciar os dados entre as tabelas de maneira adequada. Saber o uso de restrições de chave estrangeira é muito essencial para designers de banco de dados. Espero que este artigo ajude os novos projetistas de banco de dados a entender o conceito de restrições de chave estrangeira e aplicá-las adequadamente em suas tarefas.