I have a database with the following tables:

Airport ( iata PK, airport, city, state, country, lat, long)

cancellation_cause ( cod_cancellation PK, description)

Manufaturer (id_manufacturer PK, manufacturer_name)

Model (id_model PK, model_name, id_manufacturer FK)

Airline( airline_code PK, description)

airplane_type (id_AirplaneType PK, airplane_type)

engine_type (id_engine PK, engine_tyoe)

Aircraft_type (id_aircraft PK, aircraft_type)

Airplane (TailNumber PK, id_model FK, id_aircraft FK, airline_code FK, id_AirplaneType FK, id_engine FK, Issue_date, status, year)

Flight (id_flight PK, cod_cancellation FK, TailNumber FK, iata_origin FK, iata_destin FK, Year, Month, DayofMonth, DayofWeek, DepTime, CRSTime, ArrTime, CRSArrTime, FlightNum, AtualElapsedTime, CRSElapsedTime, AirTime, ArrDelay, DepDelay, distane, TaxiIn, TaiOut, Cancelled, Diverted, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay)

Note: PK - Primary Key; FK - Foreign Key

I have a csv file "flights" with millions of records containing information such as "TailNumber, AirlineCode, etc." for each of the flights.

Also have a supplementary csv "Airplane" with information about the plane (TailNumber, engine type, year, etc.)

In tables Flight, Airplane and Model some fields can be copied directly but I don't know how to load data from foreign keys correctly.

Someone help me?

1 Answer 1


Do you already have all the Airports? Some of them? None of them? The details depend on which case you are doing.

Two steps (per normalization table): INSERT...SELECT DISTINCT ... LEFT JOIN .. WHERE .. IS NULL to add to normalization table. Then UPDATE ... SELECT ... JOIN... to get the ids back.

The code here is aimed at the "some of them" case, bu probably works for all 3 cases.

If you actually have FOREIGN KEY constraints enabled, be careful of the order of performing operations -- else you could get errors.

  • I have many airports, not all. This code gave me idea how populate the tables using INNER JOIN or LEFT JOIN. Thanks Commented Aug 20, 2015 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.