Search code examples
mysqlsqlforeign-keysinsert-into

Connecting multiple MySQL tables with Foreign Keys

I have 3 different tables on MySQL:

  • Clients
  • Flights
  • Bookings

On Clients I have

  • Name
  • Last Name
  • ID
  • Flight Number (from Flights table)
  • Booking Number (from Bookings table)

On Flights I have

  • ID (from Clients)
  • Flight Number
  • Company
  • Date

On Bookings I have

  • ID (from Clients)
  • Booking Number
  • Hotel
  • Check-in date
  • Check-out date

I want, after creating a Client, to make what I create on Flights and Bookings tables link on the Clients.

So, each Client has an ID that is also inserted into Flights and Bookings tables. And I need to link that Clients.ID row to it's row on Flights and Bookings.

Is this possible with Foreign Keys?

I tried making Clients.ID a Primary Key and Flights.ID and Booking.ID a Foreign Key, but then when I use INSERT INTO I get :

#1452 - Cannot add or update a child row: a foreign key constraint fails

The SQL query was:

INSERT INTO clients (name, lastname, id) VALUES ('Jane', 'DOE', 123123123);

The SQL query to create the Foreign Keys was:

ALTER TABLE clients ADD CONSTRAINT fk_flightid FOREIGN KEY (id) REFERENCES flights(id);` and 

ALTER TABLE clients ADD CONSTRAINT fk_bookingid FOREIGN KEY (id) REFERENCES bookings(id);`

This is my first time coding MySQL, sorry if my explanation is messy.

Solution

  • You have created constraints that make client a child table of flights and bookings. As a consequence, you cannot create new clients (you would need to create parent records in the two other tables first).

    In your database design, client should be the parent table, with flights and bookings as children tables.

    Consider:

    ALTER TABLE flights 
    ADD CONSTRAINT fk_flights_client_id 
    FOREIGN KEY (id) REFERENCES client(id);

ALTER TABLE bookings 
    ADD CONSTRAINT fk_bookings_client_id 
    FOREIGN KEY (id) REFERENCES client(id);

    Other remarks:

    • columns Flight Number (from Flights table) and Booking Number (from Bookings table) do not make sense in the Client table. These information belong to the children table, and can be accessed through JOINs

    • I would recommend renaming columns ID to ClientID in all 3 tables; using a name that makes sense functionaly is better than a generic name.