Você está aqui: 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: 6269 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:

Field    Type               Null   Key    Default   Extra     
id       int(10) unsigned   NO     PRI    -         auto_increment
nome     varchar(45)        NO            -
email    varchar(45)        NO            -
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:

Field     Type                Null    Key   Default    Extra
id        int(10) unsigned    NO      PRI   -          auto_increment
titulo    varchar(45)         NO            -
paginas   int(10) unsigned    NO            -
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:

Field      Type               Null   Key    Default     Extra        
id_autor   int(10) unsigned   NO     PRI    -           -
id_livro   int(10) unsigned   NO     PRI    -           -
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 ::: Joins (Junções)

Como usar joins no MySQL

Quantidade de visualizações: 10249 vezes
As junções (joins) são ferramentas presentes na maioria dos bancos de dados que suportam SQL e são usadas quando precisamos recuperar dados de uma ou mais tabelas com base em suas relações lógicas. Desta forma, é possível combinar os registros de tais tabelas de forma a construir um "super-registro", que nos permitirá exibir relatórios mais elaborados.

Para o bom entendimento de junções, vamos considerar duas tabelas: filmes e generos. Aqui nós temos uma cardinalidade de 1 x N. Um filme possui um gênero, enquanto um gênero pode abranger vários filmes. Vamos começar criando estas duas tabelas (comece com a tabela generos, já que esta não depende da tabela de filmes):

Comando DLL CREATE TABLE para a tabela generos:

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.

Veja agora o comando SQL para a criação da tabela de filmes:

Comando DLL CREATE TABLE para a tabela filmes:

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.

Veja que a tabela filmes contém uma chave estrangeira referenciando a chave primária da tabela generos. Isso nos permite "atrelar" um filme ao seu gênero. Vá em frente e insira alguns dados em ambas as tabelas. Primeiro cadastre alguns gêneros e em seguida alguns filmes.

Vejamos agora a importância dos joins. Observe o resultado de um comando DML SELECT na tabela filmes:

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.

Nesta query o gênero é retornado como um valor inteiro, ou seja, o valor do campo id da tabela generos. Em muitos casos este não é o comportamento que queremos. Em vez do id do gênero nós gostaríamos de exibir seu nome. Isso pode ser conseguido da seguinte forma:

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.

Nesta query eu usei o nome completo da tabela antes do nome dos campos a serem retornados. Na prática, é comum darmos apelidos às tabelas. 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.

Neste exemplo, não usamos as palavras-chaves INNER JOIN, LEFT JOIN, RIGHT JOIN, etc. A junção está "escondida" na cláusula SELECT. Esta técnica é conhecida como "junção implícita" ou "implicit join". Veja como o mesmo resultado pode ser obtido usando a junção INNER JOIN:

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.

Veja minhas outras dicas sobre junções para aprender mais sobre INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, FULL JOIN, etc.


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

Como adicionar dias a uma data usando a função ADDDATE() do MySQL

Quantidade de visualizações: 13078 vezes
A função ADDDATE() é usada para adicionar dias a uma data. Veja um exemplo de seu uso:

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.

Aqui o valor do campo vencimento será acrescido de 30 dias. É possível também fornecer um número negativo de dias. Neste caso a data será decrementada pelo número de dias fornecidos. 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.


Se quiser, você pode ainda usar esta função para adicionar não somente dias, mas também meses, anos, semanas, etc. Veja um exemplo no qual adicionamos três meses a uma determinada data:

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 ::: 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: 82800 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:

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, 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:

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 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:

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.
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 ::: Chaves, Índices e Restrições de Integridade Referencial

Como excluir a chave primária de uma tabela MySQL usando o comando ALTER TABLE DROP PRIMARY KEY

Quantidade de visualizações: 5807 vezes
Em algumas situações, talvez com o propósito de realizar uma manutenção em um determinado banco de dados MySQL, nós precisamos remover a chave primária de uma tabela. Para isso podemos usar o comando DDL ALTER TABLE DROP PRIMARY KEY. Comece analisando a seguinte tabela "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.
Veja que o campo id é do tipo int, auto-incremento e foi marcado como chave primária da tabela. Assim, se por alguma razão quisermos remover esta chave primária, basta dispararmos o comando ALTER TABLE DROP PRIMARY KEY nesta tabela. 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.

Sei. O comando não funcionou como esperávamos. O MySQL nos mostrou a seguinte mensagem de erro:

Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key.

Este erro nos informa de que uma tabela MySQL só pode ter uma coluna auto-incremento, e esta deve, obrigatoriamente, ser marcada como chave primária. Assim, precisamos remover o atributo auto-incrememento antes. 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.

Pronto. Agora que já retiramos o auto-incremento do campo, o comando ALTER TABLE DROP PRIMARY KEY já pode ser disparado novamente.

Você deverá ter um cuidado especial se o campo que você vai remover o auto-incremento e chave primária estiver sendo referenciado como chave estrangeira em outra(s) tabela(s). Analise estas situações antes. É próvável que o MySQL exiba um monte de mensagens de erro até você descobrir a causa da falha do comando ALTER TABLE DROP PRIMARY KEY.


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: 4985 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.



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