Problem inserting data into a table containing foreign keys

Question

Problem inserting data into a table containing foreign keys

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Hello, everyone!

Well ... My problem is this: I have a MySQL database that has 3 tables (Client, Event, Equipment). The event table has two foreign keys, the first one referencing the id of the Customer table (customer_id) and the second referencing the id of the Equipment table. When inserting values in the Client and Equipment tables everything is OK, but when inserting values in the Event table, referencing a specific id of the other tables, the inserted values also reference the other registered ids.

To be clearer:

An example : I am going to register an event contracted by customer id 1, put all the data in their respective places, including the id number of 1 where the foreign key value . The code is executed, however, when I execute a "Select" to check, the inserted data is also referencing the id 2 client.

"Select" displaying the values entered in the Event table:

This"Select" shows the problem. The value "Saint Anthony" in the "city" column should belong only to the id 1 client.

PS: I'm sorry for any mistake, I'm new here, I've never even participated in question and answer sites. Anyway ... Can you help me? What should I do? Because I'm facing this

mysql sql-select chave-estrangeira sql-insert

asked by anonymous 30.07.2018 / 03:46

2 answers

0

Two things you need to define before writing the bank is normalization. If the customer can only hire for his city or another. In case of being to another, your event table should point to a city table (IBGE).

The bank for which you explained would be:

create table Cliente(id integer, nome varchar(100), cidade varchar(100));
insert into Cliente(id, nome, cidade) values(1, "jose" , "sao jose");
insert into Cliente(id, nome, cidade) values(2, "maria" , "sao paulo");



create table Equipamento(id integer, descricao varchar(100));
insert into Equipamento(id, descricao) values(1, "telao");

create table Evento(id integer, clienteId integer, equipamentoId integer, dia date);
insert into Evento(id, clienteId,equipamentoId) values(1, 1,1);


select e.id,c.nome,c.cidade,q.descricao 
from Evento e 
join Cliente c ON e.clienteId = c.id 
join Equipamento q ON equipamentoId = q.id;

02.08.2018 / 04:11

How to search for Id in mongodb With condition Problem in the Controller JSP class

score 1 · Accepted Answer

The way you did it:

SELECT id_evento,nome,numero,cidade,dia,horario FROM evento,cliente WHERE id_evento = 1;

It will concatenate the tuple of the evento table with id_evento = 1 with each of the tuples of the cliente table.

Doing this (using INNER JOIN ):

SELECT * FROM evento e INNER JOIN cliente c ON (e.fk_cliente = c.id_cliente)

You will only concatenate the tuples of the evento table with the data of the cliente table according to the condition given in the ON command, which in the case is when the foreign key of evento ( fk_cliente ) is equal to id_cliente present in the cliente table.