Você está aqui: MySQL ::: Dicas & Truques ::: Chaves, Índices e Restrições de Integridade Referencial

Como criar chaves estrangeiras no MySQL - Como criar Foreign Keys em tabelas do MySQL

Quantidade de visualizações: 82895 vezes
O que é chave estrangeira (foreign key)?

O papel da chave estrangeira é manter uma referência a um registro presente em outra tabela. Imagine o seguinte cenário. Temos uma tabela livros e uma tabela autores. Neste cenário, um autor pode escrever vários livros e um livro pode ser escrito somente por um determinado autor. Aqui temos uma relação 1:N, ou seja, um para muitos: um autor pode escrever zero, um ou vários livros.

Comece analisando a tabela autores:

Field    Type               Null   Key    Default   Extra    
id       int(10) unsigned   NO     PRI    -         auto_increment    
nome     varchar(45)        NO            -                
email    varchar(45)        NO            -                
Como podemos ver, esta tabela possui três campos: id, nome e email. O campo id é do tipo int, auto-incremento e é a chave primária da tabela (não poderá haver ids repetidos nem o valor NULL). Esta tabela foi criada com o seguinte comando DDL CREATE TABLE:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Analise agora a tabela livros:

Field      Type                Null    Key    Default    Extra    
id         int(10) unsigned    NO      PRI    -          auto_increment    
titulo     varchar(45)         NO             -               
paginas    int(10) unsigned    NO             -               
id_autor   int(10) unsigned    NO             -               
Note que esta tabela possui os campos id, titulo, paginas e id_autor. Veja o comando DDL CREATE TABLE usado para sua criação:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

O campo id é do tipo int, auto-incremento e foi marcado como chave primária. Dessa forma, o campo id identifica unicamente cada livro. O campo id_autor é do tipo int e sua função é guardar o id do autor que escreveu um determinado livro. Ao trazer o valor do campo id da tabela autores para o campo id_autor da tabela livros nós estamos relacionando as duas tabelas. O campo id_autor, neste cenário, é a chave estrangeira, pois seu valor sempre refletirá o valor do campo id da tabela autores (a única exceção é quando queremos deixar, temporariamente, um livro sem autor). Lembre-se, em uma relação 1:N, a chave estrangeira, em geral, ficará no lado N da relação.

Integridade Referencial (Restrições de Chave Estrangeira) - Referential Integrity (Foreign Key Constraints)

Imagine agora que você inseriu alguns registros na tabela autores e na tabela livros. Todas as vezes que o valor do campo id_autor na tabela livros for igual ao valor do campo id na tabela autores nós estaremos criando um relação autor-livro. É possível, a qualquer momento, listar um livro e saber de imediato o id do seu autor (isso permite fazer um join com a tabela autores para obter os dados do respectivo autor).

Mas, o que acontecerá se excluirmos um registro na tabela autores e, mais tarde, descobrirmos que o campo id_autor da tabela livros guardava uma referência para o autor excluído? Teremos a quebra da integridade referencial. Para evitar tais situações, é responsabilidade do programador escrever códigos de verificações para prevenir estas ocorrências.

Os bancos de dados, e principalmente o MySQL, possuem mecanismos para reforçar esta proteção: restrições de chave estrangeira. As restrições de chave estrangeira asseguram duas situações possíveis:

1) Não permitir que um autor seja excluído quando qualquer livro possuir uma referência a ele;

2) Se o autor for excluído, todos os livros que o referenciam também o serão.

Definindo a chave estrangeira na tabela livros usando o atributo CONSTRAINT FOREIGN KEY REFERENCES

Vamos agora reescrever o comando DDL CREATE TABLE para a tabela livros de forma a aplicar as restrições de chaves estrangeiras. Veja a nova versão:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

A estrutura da tabela livros será alterada para aquela mostrada abaixo:

Field      Type                Null   Key    Default    Extra    
id         int(10) unsigned    NO     PRI    -          auto_increment    
titulo     varchar(45)         NO            -              
paginas    int(10) unsigned    NO            -                
id_autor   int(10) unsigned    NO     MUL    -
Veja que agora o campo id_autor foi sinalizado como MUL, ou seja, parte de um índice não único. Experimente agora inserir dados em ambas as tabelas e faça relacionamentos entre autores e livros. Tente excluir um autor que tenha um livro relacionado a ele. Imediatamente o MySQL abortará a operação com a seguinte mensagem de erro:

ErrorNr. 1451: Cannot delete or update a parent row: a foreign key constraint fails (`estudos/livros`, CONSTRAINT `livros_autores` FOREIGN KEY (`id_autor`) REFERENCES `autores` (`id`))

Em mais dicas desta seção você aprenderá a usar as cláusulas ON DELETE e ON UPDATE e as ações RESTRICT, SET NULL, CASCADE e NO ACTION. Todas estas cláusulas e ações são usadas para reforçar a integridade referencial de suas bases de dados.


MySQL ::: Dicas & Truques ::: Joins (Junções)

Como agrupar dados de duas ou mais tabelas no MySQL usando LEFT JOIN (ou LEFT OUTER JOIN)

Quantidade de visualizações: 12847 vezes
A junção LEFT JOIN (ou LEFT OUTER JOIN) do MySQL é usada quando queremos agrupar dados de duas ou mais tabelas e exibir todos os registros da tabela à esquerda, mesmo que não haja correspondências (match) de registros na tabela à direita.

Vamos ver um exemplo? Considere duas tabelas: jogadores e times. Um jogador pode jogar em nenhum (zero) ou um time e um time pode conter zero ou mais jogadores. Aqui a cardinalidade é de 1 x N. Comece criando a tabela de times:

Comando DDL CREATE TABLE para a tabela times:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Vá em frente e insira alguns times nesta tabela. Vamos agora criar a tabela de jogadores:

Comando DDL CREATE TABLE para a tabela jogadores:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Note que o campo id_time da tabela jogador aceita NULL, ou seja, como é possível haver jogadores sem times, devemos ser capazes de cadastrar os jogadores e só mais adiante definir o time a qual ele pertencerá. Veja:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Vá em frente e cadastre alguns jogadores (sem relacioná-los com algum time). Finalizado alguns cadastros de jogadores e times, use a instrução UPDATE para relacionar alguns jogadores com seus respectivos times (deixe alguns jogadores sem time). Veja um exemplo:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

E aqui já podemos ver um exemplo da junção LEFT JOIN. Nossa tarefa é listar o id, nome do jogador e nome do time a qual ele pertence. Mas, queremos também incluir na listagem os jogadores que ainda não possuem times (o valor do campo id_time ainda é NULL). Veja:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Note que o conjunto de dados retornados inclui todos os jogadores, incluindo aqueles para os quais nenhum time foi definido ainda.


MySQL ::: Dicas & Truques ::: Chaves, Índices e Restrições de Integridade Referencial

Como criar chaves primárias compostas em uma tabela do MySQL

Quantidade de visualizações: 6292 vezes
Sabemos que o uso do atributo PRIMARY KEY (PK) permite marcar um campo de uma tabela MySQL como chave primária. Assim, este campo não poderá ter valores repetidos nem conter o valor NULL.

Há, no entanto, situações nas quais precisamos marcar mais de um campo como chave primária, ou seja, a chave primária é composta de dois ou mais campos. Estas situações surgem nos cenários em que temos relacionamentos N x N (muitos para muitos) e uma tabela associativa que represente o relacionamento.

Um exemplo disso é a relação autor-livro: um autor pode escrever vários livros e um livro pode ser escrito por mais um autor (vários autores em conjunto). Mas, o mesmo autor não pode aparecer no mesmo livro mais de uma vez. Vamos representar isso passo-a-passo.

Comece criando a tabela autores. Veja o comando CREATE TABLE completo para esta tarefa:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.


Este comando CREATE TABLE vai gerar a seguinte estrutura:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.
Note que defini o engine como InnoDB, uma vez que este tipo de armazenamento permite o uso de restrições de chaves estrangeiras, diferente do armazenamento MyISAM.

Vamos agora criar a tabela livros. Veja o comando CREATE TABLE completo:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Este comando CREATE TABLE vai gerar a seguinte estrutura:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.
Pronto. Agora já podemos criar a tabela de ligação ou associativa que fará a ponte entre o autor e o livro que ele escreveu. Veja o comando CREATE TABLE que cria a tabela autores_livros:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Este comando CREATE TABLE vai gerar a seguinte estrutura:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.
Note que nesta tabela eu criei as chaves estrangeiras (FOREIGN KEY) e apliquei as restrições de integridade referencial, ou seja, não será possível excluir um autor ou livro se seus ids estiverem registrados na tabela autores_livros.

Agora experimente inserir dados nas tabelas autores e livros. Em seguida faça o relaciomento na tabela autores_livros. Tente repetir o id do autor para o mesmo livro. Imediatamente o MySQL recusará a inserção com a mensagem de erro:

Error 1062: Duplicate entry '2-2' for key 1

E, como usamos chaves estrangeiras na tabela autores_livros, ao tentarmos excluir um livro já relacionado com um autor, teremos a seguinte mensagem de erro:

Cannot delete or update a parent row: a foreign key constraint fails (`estudos/autores_livros`, CONSTRAINT `FK_autores_livros_2` FOREIGN KEY (`id_livro`) REFERENCES `livros` (`id`))

Veja mais dicas nesta seção para aprender mais sobre chaves estrangeiras e restrições de integridade referencial.


MySQL ::: Dicas & Truques ::: Chaves, Índices e Restrições de Integridade Referencial

Como adicionar uma chave primária a uma tabela MySQL usando o comando ALTER TABLE ADD PRIMARY KEY

Quantidade de visualizações: 5019 vezes
Em algumas situações poderá ocorrer a necessidade, talvez por motivos de manutenção na base de dados, de criar uma chave primária para uma tabela MySQL já existente. Isso pode ser feito com o auxílio do comando DDL ALTER TABLE ADD PRIMARY KEY.

Comece criando uma tabela livros sem chave primária. Veja o comando CREATE TABLE:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Se dispararmos um comando DESCRIBE livros veremos a seguinte estrutura:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.
Como podemos ver, a tabela livros realmente não possui uma chave primária. Sendo assim, vamos usar o comando ALTER TABLE ADD PRIMARY KEY para adicionar uma chave primária ao campo id. Veja:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Dispare o comando DESCRIBE livros novamente e veja o resultado. Agora o campo id está marcado como chave primária:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.



MySQL ::: Dicas & Truques ::: Data e Hora

Como formatar campos DATE, TIME, DATETIME e TIMESTAMP usando a função DATE_FORMAT() do MySQL

Quantidade de visualizações: 24206 vezes
A função DATE_FORMAT() é usada quando precisamos formatar os valores obtidos de campos do tipo DATE, TIME, DATETIME e TIMESTAMP. Veja um exemplo no qual obtemos o valor de um campo DATE chamado vencimento e o formatamos para o formato 30/10/2007:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

A relação de caracteres que podem ser usados na definição do formato para esta função pode ser encontrada em:

http://www.arquivodecodigos.com.br/
referencias/mysql/
caracteres_formatacao_data_hora.php


MySQL ::: Dicas & Truques ::: Data e Hora

Como adicionar horas, dias, semanas, meses, anos, etc, ao valor de um campo DATE ou DATETIME usando a função DATE_ADD() do MySQL

Quantidade de visualizações: 9763 vezes
A função DATE_ADD() é muito útil quando precisamos adicionar horas, dias, semanas, meses, etc, ao valor de um campo do tipo DATE ou DATETIME. Esta função é composta de três partes:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

O argumento date deve ser do tipo DATE ou DATETIME. O argumento expr indica um número inteiro que indica a quantidade de horas, dias, meses, etc, que será usada como intervalo. O argumento unit indica a unidade a ser usada. Valores possíveis são: HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, etc.

Veja um exemplo no qual adicionamos 15 dias à data atual:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

Suponha que você tenha um campo chamado data_hora_compra do tipo DATETIME e que este campo tenha o valor 2008-03-30 02:30:15. A query:

Super Pack 10.000 Dicas e Truques de Programação e 500 Exercícios Resolvidos de Programação Este trecho de código ou resolução de exercício está disponível somente para apoiadores

Mas se você não puder nos apoiar agora, role a página e encontrará muito conteúdo gratuíto.

retornará 2008-05-30 02:30:15.


Veja mais Dicas e truques de MySQL

Dicas e truques de outras linguagens

Quem Somos

Osmar J. Silva
Programador Freelancer
WhatsApp +55 (062) 98553-6711

Goiânia-GO
Programador Freelancer - Full Stack Developer, Professional Java Developer, PHP, C/C++, Python Programmer, wxWidgets Professional C++ Programmer, Freelance Programmer. Formado em Ciência da Computação pela UNIP (Universidade Paulista Campus Goiânia) e cursando Engenharia Elétrica pela PUC-Goiás. Possuo conhecimentos avançados de Java, Python, JavaScript, C, C++, PHP, C#, VB.NET, Delphi, Android, Perl, e várias tecnologias que envolvem o desenvolvimento web, desktop, front-end e back-end. Atuo há mais de 15 anos como programador freelancer, atendendo clientes no Brasil, Portugal, Argentina e vários outros paises.
Entre em contato comigo para, juntos, vermos em que posso contribuir para resolver ou agilizar o desenvolvimento de seus códigos.
José de Angelis
Programador Freelancer
WhatsApp +55 (062) 98243-1195

Goiânia-GO
Programador Freelancer - Formado em Sistemas de Informação pela Faculdade Delta, Pós graduado em Engenharia de Software (PUC MINAS), Pós graduado Marketing Digital (IGTI) com ênfase em Growth Hacking. Mais de 15 anos de experiência em programação Web. Marketing Digital focado em desempenho, desenvolvimento de estratégia competitiva, analise de concorrência, SEO, webvitals, e Adwords, Métricas de retorno. Especialista Google Certificado desde 2011 Possui domínio nas linguagens PHP, C#, JavaScript, MySQL e frameworks Laravel, jQuery, flutter. Atualmente aluno de mestrado em Ciência da Computação (UFG)
Não basta ter um site. É necessário ter um site que é localizado e converte usuários em clientes. Se sua página não faz isso, Fale comigo e vamos fazer uma analise e conseguir resultados mais satisfatórios..

Linguagens Mais Populares

1º lugar: Java
2º lugar: C#
3º lugar: Delphi
4º lugar: PHP
5º lugar: Python
6º lugar: JavaScript
7º lugar: C
8º lugar: C++
9º lugar: VB.NET
10º lugar: JSP (Java Server Pages)



© 2021 Arquivo de Códigos - Todos os direitos reservados | Versión en Español | Versão em Português