Foreign keys

Infraestrutura Banco de Dados
#1

Eu tenho duas tabelas (usuario e modificacoes)

Eu quero que quando eu for cadastrar uma modificacao e selecionar o gerente, já trazer os dados referentes ao usuario (gerente, team e nivel).
Estou tentando aqui com chave estrangeira, mas nao ta indo…to anos sem programar…to sem a logica do negocio kkkk
Alguem poderia ajudar? vlw

Tabela usuario

CREATE TABLE `usuario` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `authority` varchar(20) NOT NULL,
  `name` varchar(70) NOT NULL,
  `password` varchar(100) NOT NULL,
  `username` varchar(40) NOT NULL,
  `gerente` varchar(50) NOT NULL,
  `team` varchar(10) NOT NULL,
  `nivel` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

Tabela modificacoes

CREATE TABLE `modificacoes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_impact` text NOT NULL,
  `short_description` text NOT NULL,
  `system` varchar(10) NOT NULL,
  `mod_number` varchar(30) NOT NULL,
  `customer` varchar(50) NOT NULL,
  `end_date` datetime NOT NULL,
  `start_date` datetime NOT NULL,
  `status` varchar(30) DEFAULT NULL,
  `user_create` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
#2 
CREATE TABLE `usuario` (
  `idUser` int(11) NOT NULL AUTO_INCREMENT,
  `authority` varchar(20) NOT NULL,
  `name` varchar(70) NOT NULL,
  `password` varchar(100) NOT NULL,
  `username` varchar(40) NOT NULL,
  `gerente` varchar(50) NOT NULL,
  `team` varchar(10) NOT NULL,
  `nivel` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

Na tabela modificações você precisa por o ID do usuário que modificou alguma coisa:

CREATE TABLE `modificacoes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idUser` Int NOT NULL,
  `business_impact` text NOT NULL,
  `short_description` text NOT NULL,
  `system` varchar(10) NOT NULL,
  `mod_number` varchar(30) NOT NULL,
  `customer` varchar(50) NOT NULL,
  `end_date` datetime NOT NULL,
  `start_date` datetime NOT NULL,
  `status` varchar(30) DEFAULT NULL,
  `user_create` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

Depois criar a relação entre as tabelas com o FK criado:

Alter table modificacoes add Foreign Key (idUser ) references usuario (idUser) on delete cascade on update cascade;

Você pode remover o trecho on delete cascade on update cascade se achar necessário, ali estamos dizendo que quando um usuário for removido, as modificações que estão gravadas na tabela filho ‘modificacoes’, também serão removidas.

2 curtidas
#3

Show de bola, era isso mesmo.

Agora só preciso codificar no meu projeto e ver se vai ser isso mesmo.

Valeu

#4

Pensando aqui se é isso mesmo… para cada gerente, são vários funcionários e times diferentes. No formulario de modificacoes, se eu selecionar o gerente, ele vai trazer os usuarios e teams?

#5

Sim, basta filtrar:

select m.*, u.* from modificacoes m, usuario u
where u.gerente = 'Gerente'
and m.idUser = u.idUser;

Isso vai buscar as modificações dos usuários que possui o gerente ‘Gerente’;

#6

Entendi… mas tem alguma ideia que eu ainda nao estou conseguindo formar… que é na hora de cadastrar uma modificação.
Nao fica estranho na hora que for fazer o insert das modificacoes e ter que colocar o idUser? Como vou selecionar uma ID sendo que nao vou saber a quem se refere aquela ID…porque vai ter mtos…
Talvez se escrever o username seria mais pratico…sera q nao? mas ai teria q mudar a chave primaria da tabela usuario para o username neh?

Oq vc acha? vlw

#7

Não, tem que ser o id mesmo.

Imagine que você grave a modificação para o usuário ‘João’ qu tme o id ‘5’, na tabela modificacoesvocê guarda o 5. Toda vez que você alterar um dado do usuário 5, não vai mudar nada na tabela modificacoes, porque está atrelado ao id.

Se você guardar o nome como chave, pode acontecer de existir 2 João, e o banco disparar um Exception dizendo que a chave foi violada.

Sobre como saber o id do usuário, aí eu não sei como está funcionando a estrutura da sua aplicação. Esse usuário seria alguém logado?

#8

Sim, usuario logado…uso spring security

a tabela modificacoes é referente a atualizacoes em servidores que sao feitas (entao fica tudo registrado que no servidor X foram feitas tais atualizacoes)

Mas entao como eu faco de uma maneira mais intuitiva para add essa id sem ser ter que colocar o numero dela?

#9

simplesmente não existe como, é uma chave estrangeira, você precisa colocá-la!

#10

Me expressei errado… a ID vou ter que colocar, claro…mas digo de outra forma…mostrando uma string do username…sei la… acho que nao da mesmo

se o usuario está logado…eu puxando a id dele ja dentro do campo…pode dar certo tbn eh?

#11

Sim, você pode colocar a id do usuário logado.

Se a sua preocupação é mostrar o nome do usuário que fez a alteração, isso não é problema, basta aplicar a seleção que lhe mostrei acima, você filtra o nome do usuário através da id dele.

#12

Na questao de mostrar, seria só quando for mostrar os dados na tabela e no relatorio mesmo.

Pra mim o importante é na hora que for cadastrar uma modificacao… porque vou ter que adicionar o idUser e nao fica muito intuitivo ter que add o numero da ID do usuario .
Pensando aqui… se eu conseguir colocar o id do usuario logado já no campo, ótimo…resolve o problema, mas posso tambem fazer um select e mostrar o ID do usuario e ele só digita no campo o user dele, né?

#13

Dessa forma você cria vulnerabilidade no sistema.
O usuário 3 pode apagar uma lista de clientes e colocar o código do usuário 5.
Não há motivos para que o usuário forneça esse tipo de informação, uma vez que tem o idUser do usuário ativo logado, todas as modificações feitas por ele, serão registradas.

#14

Blz…Valeu Junior!

#15

a dica que dou tbm na hora da criação de tabelas é que previamente vc faça uma modelagem de dados assim evitando um monte de transtornos, veja:

CREATE TABLE `modificacoes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idUser` Int NOT NULL,
  `business_impact` text NOT NULL,
  `short_description` text NOT NULL,
  `system` varchar(10) NOT NULL,
  `mod_number` varchar(30) NOT NULL,
  `customer` varchar(50) NOT NULL,
  `end_date` datetime NOT NULL,
  `start_date` datetime NOT NULL,
  `status` varchar(30) DEFAULT NULL,
  `user_create` varchar(40) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `ID_USER` FOREIGN KEY (`idUser`) REFERENCES `usuario` (`idUser`)
       ON DELETE CASCADE
       ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

assim vc já mata o ALTER TABLE, deixando ele apenas para alguma coisa bastante necessária, outra dica é, nomeie suas chaves com nomes diferentes assim se vc precisar criar alguma Trigger ou procedure ou até mesmo o algum sql vc ñ se perca.