mysqlsqlforeign-keysinsert-into

Connecting multiple MySQL tables with Foreign Keys

I have 3 different tables on MySQL:

On Clients I have

On Flights I have

On Bookings I have

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: