Populate tables with multiple foreign keys

Question

I have a database with the following tables:

• Airport ( Iata PK, airport, city, state, country, lat, long)

• Causa_cancellation ( code PK, description)

• Manufacturer (Idfabricante PK, manufacturer name)

• Model (Idmodelo PK, model name, manufacturer name FK)

• Airline(code PK, description)

• Tipoaviao (Idtipoaviao PK, plane type)

• Engine type (Idtipo_motor PK, engine type)

• Tipolicenca (Idtipo_licenca PK, typo_licenca)

• Aviao (codAviao PK, Idmodelo FK, Idtipo_licenca FK, Code-keeping FK, Idtipoaviao FK, Idtipo_motor FK, date registration, state, year)

• Flight (Idvoo PK, code FK, CodaviaoFK, iata_origin FK, iata_destination FK, Datavoo, Hora_partida_real, Hora_partida_prevista, Hora_chegada_real, Hora_chegada_prevista, FlightNum, AtualElapsedTime, TempoVoo, distancia, cancelado)

Note: PK - Primary Key; FK - Foreign Key

The data source is files csv. The csv main has millions of records and all flight information. The rest are files csv with information about the plane, airline and airport.

My question is: what is the simplest/most effective way to correctly load foreign key data from the Airplane and Flight tables?

Answer 1

In Mysql, you can use a feature of it called LOAD DATA INFILE.

The LOAD DATA INFILE statement Allows you to read data from a text file and import the file’s data into a database table very fast.

With it, you can directly upload a CSV from your file system and pass to a table from the Mysql database.

If you have an Aviao table (abstract some fields because I don’t have the details of your table):

CREATE TABLE Aviao (
  codAviao INT NOT NULL AUTO_INCREMENT,
  data_registro DATE NOT NULL,
  nome VARCHAR(50) NOT NULL
  PRIMARY KEY (codAviao )
);

And a CSV called.csv airplanes, with this content:

codAviao, data_registro, nome
1, 20150809, "Aviao 1"
2, 20140809, "Aviao 2"

You can import this way (with the.csv file located in c:/tmp/):

LOAD DATA INFILE 'c:/tmp/avioes.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

More details you can find here: http://www.mysqltutorial.org/import-csv-file-mysql-table/

To ensure the integrity of the information at the end of the process, since you will (probably) need to enter information from related tables not yet imported in one of the imports, it is sufficient at the beginning of the import to disable the foreign key check:

SET FOREIGN_KEY_CHECKS=0;

and then enable again, at the end of the import:

SET FOREIGN_KEY_CHECKS=1;