I have a scenario where i need to read contents from an excel file. In the excel file there is column which tell if the a row is primary or not. Others will be sub rows of the primary row. I need to insert primary in one table. Get the last insertId and store the sub rows in another table. But always am getting some error related to "child foreign key stuff". Below is the approach am following
Reading the contents for excel with below code
let reader = require('xlsx');
module.exports.storeData = () =>{
const file = reader.readFile('./data/file.xlsx');
let data = []
const sheets = file.SheetNames
// for(let i = 0; i < sheets.length; i++)
// {
const temp = reader.utils.sheet_to_json(
file.Sheets[file.SheetNames[sheets.indexOf('sheet-name')]])
temp.forEach((res) => {
data.push(res)
})
// }
this.pushToDB(data);
}
Below is how am checking if the row is primary and pushing data to 1 table else to the other table. Am also using async. But not getting last_insertId Asynchronously. Some Main table data gets inserted but not Sub Table data
let last_insert_id = 0;
const executeQuery = async (con, query, params) => {
return new Promise((resolve, reject) => {
con.query(query, params, (err, result) => {
if (err) { return reject(err); }
return resolve(result);
});
});
}
module.exports.pushToDB = async (data) => {
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "data-db"
});
con.connect(async function(err) {
if (err) throw err;
console.log("Connected!");
data.forEach(async (res, ind) => {
try {
if(res['Primary_Col'] == 'Yes'){
let param = ['col1']
const query1 = "INSERT INTO Main_Table (COL1) VALUES (?)";
const result = await executeQuery(con, query1, param);
last_insert_id = result.insertId;
console.log(last_insert_id);
}
if(res['Primary_Col'] == 'No'){
let param = ['col1', 'col2', 'col3' ]
const query1 = "INSERT INTO SUB_TABLE (COL1, COL2, COL3) VALUES (?,?,?)";
const result = await executeQuery(con, query1, param.map(String)); //param array values will be changes to string
}
} catch (err) {
con.destroy;
throw err;
}
});
});
}
this.storeData();
The exact error
Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails