Problem inserting data into a table containing foreign keys

Asked

Viewed 215 times

0

Hello, everybody!

Well... here’s my problem: I have a Mysql database that has 3 tables (Client, Event, Equipment). The event table has two foreign keys, the first of them referencing the id of the Client table (id_client) and the second referencing the id of the Equipment table. When entering values in the Client and Equipment tables everything ok, but when entering values in the Event table, referencing a specific id of the other tables, the inserted values also reference the other registered id’s.

To be clearer:

An example: I will register an event contracted by the id 1 client, put all the data in their respective places, including the id number equal to 1 where the foreign key value has to be. The code is executed, but when I run a "Select" to check, the entered data is also referencing the id client 2.

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

This "Select" shows the problem. The value "Santo Antônio" in the "city" column should belong only to the id 1 account. Printscreen 2

PS: I’m sorry for any mistakes, I’m new here, and I’ve never even participated in question and answer websites. Anyway... Can you help me? What should I do? Why am I facing this

mysql database select-sql sql-insert foreign-key

  • The problem is that by making one SELECT comes the result of all Cliente ? I’m sorry, but I don’t understand your problem.

  • No, @Thiagomagalhães. I’ll use the example I used in the post. The problem is as follows: Suppose I have registered an event contracted by an id client 1. The entered data (using INSERT INTO), instead of referencing only the id client 1 (which was passed as the primary key value in INSERT INTO) also reference the id client 2. That is, all information of the event contracted by the id client 1, are registered as if they were also id client 2. Got it?

  • understood. can put the structure of tables?

  • Yes, @Thiagomagalhães. I will post one at a time because of the character limit.

  • create table client( id_client int auto_increment not null, name varchar(35), numero varchar(15), Primary key(id_client) );

  • create table equipment( id_equipment int auto_increment not null, name varchar(8), Primary key(id_equipment) );

  • create table event( id_event int auto_increment not null, endereco varchar(40), dia varchar(8), horario varchar(5), fk_client int not null, fk_equipamento int not null, Primary key(id_event), Constraint Foreign key (fk_client) Customer Resets client (id_client), Constraint Foreign key (fk_equipment) References equipment (id_equipment) );

  • You’re making the select that way: SELECT * FROM evento e WHERE e.fk_cliente = 1 ?

  • @Thiagomagalhães, take a look at the prints I just put in the post, maybe it will help you understand the problem.

  • Testing: SELECT * FROM evento e
INNER JOIN cliente c ON (e.fk_cliente = c.id_cliente). If the city repeats, it is pq more than one event in the same city were registered to different customers.

  • NOTE: I changed the name of the column "address", which was in the structure of the table "event" shown above, to "city".

  • @Thiagomagalhães, the information was displayed right now. But explain me one thing, if possible: why this problem was occurring?

  • How was the consultation you were doing?

  • @Thiagomagalhães, sorry for the delay in answering. I was making the query as follows: SELECT id_event, name, number, city, day, time FROM event, client WHERE id_event = 1; It was more or less like this, I don’t remember well.

  • 1

    By the way... Thanks a lot for the help! The way you told me solved the problem. Thank you very much, @Thiagomagalhães!

Show 10 more comments

2 answers

1


The way you did:

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

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

Doing so (using INNER JOIN):

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

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

  • That’s exactly what I wanted to know, @Thiagomagalhães! Thank you so much for the clarification and the help!

0

Two things you need to define before writing the database is normalization. Whether the client can hire only for his city or another. In case it is for another, your table event should point to a table of cities (IBGE).

The bank for what you explained would stay :

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;

  • Thanks a lot for the tip, @Israelzebulon! Helped a lot too!

Browser other questions tagged mysql database select-sql sql-insert foreign-key

You are not signed in. Login or sign up in order to post.