ABOUT MYSQL AND FOREIGN KEY

Asked

Viewed 38 times

0

Good night guys! I have a database with the tables:

  • PASSENGERS
  • MOTORISTS
  • TRAVELS
  • For each trip a driver is assigned.
  • More than one passenger can go on the same trip.

Doubt: How do I reference more than one passenger on a same trip by FK?

mysql

  • Assuming that the passenger is not indexed by the CPF and is 'disposable', that is, each passenger line is created for a single trip, it is the passenger who references the trip as a foreign key.

  • just create a table of n for n for example travelling_trips, where in this table you will store id_passenger and id_trip.

1 answer

0

You can create a table of many for many, and reference the trip and the passengers, I will post an example. Note: I did not create the table with pk's nor fk's, because it is only for you to understand the modeling and the query's.

CREATE TABLE passageiros(
    id INT, 
    nome VARCHAR(50)
);
INSERT INTO passageiros VALUES
(1,'Passageiro 1'),(2,'Passageiro 2'),(3,'Passageiro 3'),(4,'Passageiro 4'),(5,'Passageiro 5');

CREATE TABLE motoristas(id INT, nome VARCHAR(50));
INSERT INTO motoristas VALUES
(1,'Motorista 1'),(2,'Motorista 2'),(3,'Motorista 3');

CREATE TABLE viagens(id INT, id_motorista INT, descricao VARCHAR(50));
INSERT INTO viagens VALUES
(1,2,'Viagem 1'), (2,3,'Viagem 2');

CREATE TABLE passageiros_viagens(id INT, id_passageiro INT, id_viagem INT);

INSERT INTO passageiros_viagens VALUES
(1,1,1),(2,3,1),(3,5,1),(4,2,2),(5,5,2);

In case I want to know the passengers who are on trip 1, I run the following query: 

 SELECT
        p.*
    FROM viagens v
    INNER JOIN motoristas m
    ON m.id = v.id_motorista
    INNER JOIN passageiros_viagens pv
    ON pv.id_viagem = v.id
    INNER JOIN passageiros p
    ON p.id = pv.id_passageiro
    WHERE v.id = 1;

Or I can use it that way using the GROUP_CONCAT and bring in just one line for better viewing:

  SELECT
        v.descricao,
        m.nome,
        GROUP_CONCAT(p.nome SEPARATOR ', ') AS passageiros
    FROM viagens v
    INNER JOIN motoristas m
    ON m.id = v.id_motorista
    INNER JOIN passageiros_viagens pv
    ON pv.id_viagem = v.id
    INNER JOIN passageiros p
    ON p.id = pv.id_passageiro
    WHERE v.id = 1;

Browser other questions tagged mysql

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