Olá galera o/
Continuando nossa série de postagens sobre as restrições de domínio vulgo constraints, hoje iremos abordar sobre as foreign key.
Let’s go para nosso segundo episódio Guys!!!
O que é a Foreign Key (FK)?
Constraint que define que, uma coluna FK_COD_A contido em uma tabela B, faz referência ao atributo COD_A que é chave primária de uma tabela A.
A chave estrangeira é definida no momento em que estamos modelando os dados.
O atributo deve ser do mesmo tipo do campo referência de sua tabela de origem, ou seja, se na tabela de origem o campo é do tipo inteiro, na tabela de destino ela também deve ser do tipo inteiro.
O atributo (FK) deve ser referenciado a qual atributo da tabela de origem ele faz correlação.
Para a chave estrangeira funcionar, a tabela que contém o atributo chave deve ser criada primeiro, ou seja, para criar uma tabela de pedidos que contém uma referência a tabela de clientes, primeiro a tabela de cliente deve ser criada, para que assim posteriormente criamos a tabela de pedido.
Para um registro ser referenciado na tabela com a FK, o registro tem que existir na tabela de origem, ou seja, para inserir um registro na tabela de vendas fazendo correlação a cliente Maria, a cliente Maria deve ser inserida previamente na tabela de clientes.
Boa prática!
Sempre utilizar a ferramenta de modelagem para se guiar na criação das chaves estrangeiras. Devemos evitar utilizar os scripts gerados pela ferramenta, pois assim, deixamos de aprender a criar chaves estrangeiras nas melhores práticas.
Criar uma chave primária para tabelas que surgem a partir de relacionamentos NxN. A mesma pode ser auto incremental e é ideal para controle dos dados inseridos nesta tabela.
Para facilitar o entendimento, tudo que se referir a chave estrangeira vamos adotar a sigla FK, também unificaremos tudo que for referente a atributo, coluna ou campo, utilizaremos a palavra coluna (Column).
Praticando a criação de FKs!
A partir de agora, Começaremos a praticar a criação de FKs.
1 – Criando a modelagem
Criaremos uma modelagem de dados para nos basearmos na criação das chaves estrangeiras. Simularemos relacionamentos 1xN (um para muitos) e NxN (muitos para muitos).
Relacionamentos:
Cliente 1 x N Pedido – Uma referência de cliente será adicionada na tabela de Pedido
Vendedor 1 x N Pedido – Uma referência de vendedor será adicionada na tabela de Pedido
Pedido N x X Produto – Uma nova tabela será criada. A mesma receberá referência das suas tabelas de origem (Pedido e Produto). Chamaremos esta tabela de item_pedido, além disso, vamos adicionar um atributo chamado qtd_itens.
Caso queira saber mais sobre cardinalidades, sugiro ler estas 3 postagens.
1 – Cardinalidade – 1 x 1
2 – Cardinalidade – 1 x N ou N x 1
3 – Cardinalidade – N x N
2 – Criando tabelas com FKs – Definição no create table
2.1 – Todas a tabelas da modelagem vão ter o padrão TB_NOME_TABELA, assim, temos um padrão de nomenclatura e facilitamos o entendimento sobre as FKs. Percebam que, na tabela TB_PEDIDO existem duas colunas chamadas fk_cod_cliente que faz referência ao código da tabela TB_CLIENTE e fk_cod_vendedor que faz referência ao código da tabela TB_VENDEDOR.
Obs.: Para criarmos as tabelas que recebem as FKs, primeiro é necessário criar as tabelas a quais os atributos fazem referências, ou seja, devemos primeiro criar as tabelas TB_CLIENTE, TB_VENDEDOR e TB_PRODUTO pois elas não possuem dependência com nenhuma outra tabela. Uma vez com essas tabelas criadas, podemos criar as tabelas que possuem as FKs.
Sintaxe de uma FK:
Sem nome da constraint:
– coluna TIPO FOREIGN KEY REFERENCES tb_origem (coluna+origem_pk)
Com nome da constraint:
– coluna TIPO CONSTRAINT nome_constraint FOREIGN KEY REFERENCES tb_origem (coluna+origem_pk)
CREATE TABLE TB_CLIENTE (
codigo int PRIMARY KEY,
nome varchar (50)
);
CREATE TABLE TB_VENDEDOR (
codigo int PRIMARY KEY,
nome varchar (50),
salario smallmoney
);
CREATE TABLE TB_PRODUTO (
codigo int PRIMARY KEY,
nome varchar (50),
preco_unitario smallmoney
)
CREATE TABLE TB_PEDIDO (
num_pedido int PRIMARY KEY,
data_pedido datetime2,
fk_cod_cliente int foreign key references TB_CLIENTE (codigo),
fk_cod_vendedor int CONSTRAINT fk_vendedor foreign key references TB_CLIENTE (codigo)
);
CREATE TABLE TB_ITEM_PEDIDO (
cod_item_pedido int primary key identity,
fk_num_pedido int foreign key references TB_PEDIDO (num_pedido),
fk_cod_produto int foreign key references TB_PRODUTO (codigo),
qtd_itens int
);
2.2 – Vamos inserir 3 registros nas tabelas TB_CLIENTE, TB_VENDEDOR e 5 registros na TB_PRODUTO, ao inserir os registros vamos consultar as 3 tabelas.
insert into TB_CLIENTE (codigo, nome)
values
(1,'Joana'),
(2,'Rafaela'),
(3,'Amanda')
insert into TB_VENDEDOR(codigo, nome, salario)
values
(1,'Pedro', 1900.00),
(2,'Kleber', 2500.00),
(3,'Camilla',4560.80)
insert into TB_PRODUTO(codigo, nome, preco_unitario )
values
(1,'Smartphone LG K52', 1106.20),
(2,'Notebook gamer Dell G15', 8699.00),
(3,'Fone de Ouvido JBL Tour One',1699.00),
(4,'Teclado e mouse sem fio Dell Pro', 179.00),
(5,'Webcam Full HD Logitech', 524.90)
select * from TB_CLIENTE
select * from TB_VENDEDOR
select * from TB_PRODUTO
2.3 – Vamos inserir 4 registros na tabela TB_PEDIDO. Devemos ficar atentos aos valores utilizados nas colunas fk_cod_cliente e fk_cod_vendedor. Os valores utilizados nos inserts em tabelas que possuem colunas com FK, devem fazer referência aos códigos que existem na tabela de origem.
TB_PEDIDO
registro 1 – Cliente Amanda (cod 3), Vendedora Camilla (cod 3)
registro 2 – Cliente Amanda (cod 3), Vendedora Camilla (cod 3)
registro 3 – Cliente Joana (cod 1), Vendedor Pedro (cod 1)
registro 4 – Cliente Rafaela (cod 2), Vendedor Kleber (cod 2)
insert into TB_PEDIDO (num_pedido, data_pedido, fk_cod_cliente, fk_cod_vendedor)
values
(1000, '2022-11-10', 3 , 3),
(1001, '2022-11-11', 3 , 3),
(1002, '2022-11-12', 1 , 1),
(1003, '2022-11-10', 2 , 2)
select * from TB_PEDIDO
2.4 – Vamos inserir 6 na tabela TB_ITEM_PEDIDO. Devemos ficar atentos aos valores utilizados nas colunas fk_num_pedido e fk_cod_produto. Os valores utilizados nos inserts em tabelas que possuem colunas com FK, devem fazer referência aos códigos que existem na tabela de origem. Fiquem atentos nas tabela TB_ITEM_PEDIDO, o insert da mesma somente conterá valores nas colunas fk_num_pedido, fk_cod_produto e qtd_itens. Lembrando que, uma pedido pode conter vários produtos e um produto pode estar em vários pedidos, ou seja, uma matriz.
Obs.: A coluna cod_item_pedido neste momento estamos utilizando como auto incremental com a clausula identity. Iremos fazer uma postagem especifica explicando esta clausula.
TB_ITEM_PEDIDO
registro 1 – Pedido 1000 pertence a Cliente Amanda, neste pedido temos os produto 5 (Webcam Full HD Logitech)
registro 2 – Pedido 1000 pertence a Cliente Amanda, neste pedido temos os produto 3 (Fone de Ouvido JBL Tour One)
registro 3 – Pedido 1001 pertence a Cliente Amanda, neste pedido temos os produto 4 (Teclado e mouse sem fio Dell Pro)
registro 4 – Pedido 1002 pertence a Cliente Joana, neste pedido temos os produto 1 (Smartphone LG K52)
registro 5 – Pedido 1003 pertence a Cliente Rafaela, neste pedido temos os produto 2 (Notebook gamer Dell G15)
registro 6 – Pedido 1003 pertence a Cliente Rafaela, neste pedido temos os produto 4 (Teclado e mouse sem fio Dell Pro)
insert into TB_ITEM_PEDIDO (fk_num_pedido, fk_cod_produto, qtd_itens)
values
(1000,5, 1),
(1000,3, 1),
(1001,4, 1),
(1002,1, 2),
(1003,2, 1),
(1003,4, 1)
select * from TB_ITEM_PEDIDO
2.5 – Agora faremos um pequeno relatório dos pedidos da cliente Amanda. Devemos retornar o nome do cliente, o numero do pedido, a data do pedido, o nome do vendedor que liberou o pedido, o nome e preço do produto e a quantidade comprada. Isso só é possível por conta das FKs. Aqui entramos em outro assunto os JOINs.
Se quiser aprender mais sobre os Joins, sugiro ler esta postagem: Aprendendo a fazer JOINs: INNER, LEFT, RIGHT, FULL, CROSS E SELF
SELECT
a.nome as cliente,
e.nome as vendedor,
b.num_pedido as numero_pedido,
b.data_pedido,
d.nome as produto,
d.preco_unitario,
c.qtd_itens as qtd_comprada
FROM TB_CLIENTE as a
INNER JOIN TB_PEDIDO as b
ON a.codigo = b.fk_cod_cliente
INNER JOIN TB_ITEM_PEDIDO as c
ON b.num_pedido = c.fk_num_pedido
INNER JOIN TB_PRODUTO as d
ON c.fk_cod_produto = d.codigo
INNER JOIN TB_VENDEDOR as e
ON b.fk_cod_vendedor = e.codigo
where a.codigo = 3
3 – Adicionando FK depois de uma tabela já criada.
3.1 – Para este exemplo vamos criar 2 tabelas, uma tabela de TB_ANIMAL e uma tabela de TB_DONO. Possuímos um relacionamento 1xN, onde um animal pode possuir apenas um dono, porém, um dono pode possuir mais de um animal. Não vamos criar a constraint FK no momento do script de create table da tabela Animal, ou seja, vamos utilizar o comando alter table para realizar este procedimento.
Obs.: Na tabela de TB_ANIMAL , existe uma coluna chamada fk_cod_dono que faz referência ao cod_dono da tabela TB_DONO
create table TB_DONO (
cod_dono int primary key,
nome_dono varchar (100)
)
create table TB_ANIMAL(
cod_animal int primary key,
nome_animal varchar (100),
fk_cod_dono int
)
-- alterando a tabela TB_ANIMAL adicionando uma FK sem definir o nome da constraint.
ALTER TABLE TB_ANIMAL
ADD FOREIGN KEY (fk_cod_dono)
REFERENCES TB_DONO (cod_dono);
--alterando a tabela TB_ANIMAL adicionando uma FK definindo o nome da constraint.
-- Executar u
ALTER TABLE TB_ANIMAL
ADD CONSTRAINT fk_ANIMALDONO
FOREIGN KEY (fk_cod_dono)
REFERENCES TB_DONO (cod_dono);
3.2 – Caso queira apagar uma referência de FK de uma tabela, é necessário apagar a constraint criada pelo SGBD ou definida por você no momento da criação do código.
ALTER TABLE TB_ANIMAL
DROP CONSTRAINT fk_ANIMALDONO
4 – Criando FK na tabela – Com PK Composta.
4.1 – Exemplificaremos este cenario criando duas tabelas TB_FK1 e TB_FK2. A tabela 1 conterá uma PK Composta, para definirmos a FK na tabela 2, devemos fazer o seguinte procedimento:
Passo 1 – Definir o nome das colunas FKs com seus determinados tipos.
Passo 2 – Após o passo 1, colocar uma virgula após a ultima coluna e escrever a sintaxe
FOREIGN KEY (coluna1, coluna2) REFERENCES tabela 1 (chave1, chave2).
Passo 3 – Feito os passos 1 e 2, você terá uma FK Composta na sua tabela.
create table TB_FK1(
cod_fk1_a int,
cod_fk1_b int,
nome varchar (20),
primary key (cod_fk1_a,cod_fk1_b )
)
create table TB_FK2(
cod_fk2 int primary key,
fkcod_fk1_a int ,
fkcod_fk1_b int,
foreign key (fkcod_fk1_a,fkcod_fk1_b) references TB_FK1(cod_fk1_a, cod_fk1_b)
)
5 – Manipulando registros de tabelas com FK
5.1 – Para inserirmos ou deletarmos registros de uma tabela que possui FK devemos nos atentar a alguns fatos:
1 – Somente será possível referenciar um registro caso exista na tabela de origem.
2 – Se um registro da tabela de origem já contiver uma referência em outra tabela, o SGBD não vai permitir a exclusão do mesmo. Isto se chama INTEGRIDADE REFERÊNCIAL, ou seja, é uma maneira do SGBD garantir a integridade dos dados contidos nas tabelas e também assegurar que um registro fique sem suas devidas referências com outras tabelas.
3 – Para deletar um registro que contém referência, primeiro é necessário deleta-lo na tabela que contém a referência, para que depois você possa deletar na tabela de origem. O mesmo se aplica para exclusão de tabelas no banco de dados, ou seja, primeiro apaga a tabela que contém a FK, para que assim, possa apagar a tabela de origem
Exemplificaremos isso criando duas tabelas TB_MARCA e TB_CARRO. A tabela TB_CARRO vai receber uma referência da tabela TB_MARCA. Na tabela TB_MARCA vamos inserir 2 registros e 4 registros na tabela TB_CARRO. Depois vamos executar o comando Select para verificar os dados.
CREATE TABLE TB_MARCA(
COD_MARCA INT PRIMARY KEY,
NOME_MARCA VARCHAR(20)
)
CREATE TABLE TB_CARRO(
COD_CARRO INT PRIMARY KEY,
MODELO_CARRO VARCHAR(50),
COR_CARRO VARCHAR(20),
FK_COD_MARCA INT FOREIGN KEY REFERENCES TB_MARCA (COD_MARCA)
)
INSERT INTO TB_MARCA (COD_MARCA, NOME_MARCA)
VALUES
(1, 'Chevrolet'),
(2, 'FIAT')
INSERT INTO (COD_CARRO, MODELO_CARRO, COR_CARRO, FK_COD_MARCA)
VALUES
(1, 'Onix', 'Prata', 1),
(2, 'Tracker', 'Vermelho', 1),
(3, 'Argo', 'Preto Ouro', 2),
(4, 'Toro', 'Branco', 2)
select * from TB_MARCA
select * from TB_CARRO
5.2 – Agora, tentaremos inserir um registro na tabela TB_CARRO passando um valor na coluna FK_COD_MARCA que não existe, ou seja, vou tentar informar o valor 3, sendo que, esta marca não existe na tabela TB_MARCA.Um erro será apresentado informando erro de constraint, ou seja, ele esta informando que, você esta inserindo um código que não possui nenuma referência com a tabela de origem.
INSERT INTO TB_CARRO (COD_CARRO, MODELO_CARRO, COR_CARRO, FK_COD_MARCA)
VALUES
(5, 'T-Cross', 'Ouro', 3)
5.3 – Para resolvermos o erro, primeiro iremos inserir a marca Volkswagen com o código 3 na tabela TB_MARCA e posteriormente vamos re-executar o comando de insert novamente na tabela TB_CARRO. Vamos executar o select nomanete para vermos o resultado.
INSERT INTO TB_MARCA (COD_MARCA, NOME_MARCA)
VALUES
(3, 'Volkswagen')
INSERT INTO TB_CARRO (COD_CARRO, MODELO_CARRO, COR_CARRO, FK_COD_MARCA)
VALUES
(5, 'T-Cross', 'Ouro', 3)
select * from TB_MARCA
select * from TB_CARRO
5.4 – Tentaremos apagar o registro Volkswagen da tabela TB_MARCA. Será apresentado um erro informando que, o registro possui correlação com um registro de outra tabela.
delete from TB_MARCA
where COD_MARCA = 3
5.5 – Para resolvermos o erro, primeiro devemos apagar o registro que contém a referência na tabela TB_CARRO e posteriormente apagar o registro na tabela TB_MARCA. Vamos realizar um select para analisar o resultado do procedimento.
select * from TB_MARCA
select * from TB_CARRO
-- 1º excluir o registro com referência
delete from TB_CARRO
where COD_CARRO = 5
-- 2º excluir o registro na origem
delete from TB_MARCA
where COD_MARCA = 3
-- 3º analisar tabelas
select * from TB_MARCA
select * from TB_CARRO
5 – Considerações Finais
No episódio 02 aprendemos um pouco sobre a constraint Foreign Key, como criar uma tabela com FK simples e composta, como adicionar uma FK em uma tabela já existente,o que acontece quando tentamos inserir ou apagar um registro em uma tabela que contém a constraint aplicada e também aprendemos um pouco de integridade referêncial.
Este é o segundo de 6 posts sobre as constraints, fiquem atentos aos próximos episódios dessa série.
Espero que tenham gostado pessoal, boa leitura e bons estudos!
Quem quiser mandar comentários, sugestões, críticas ou dicas complementares, fiquem a vontade, pois feedbacks positivos ou negativos engradecem meu conhecimento para poder melhorar as postagens para vocês.
Até a próxima o/
Acessem nossas Redes Sociais:
Consulta BD 