How can ı save automaticcly generated primary key as a foreign key in another table in MySQL?

Question

I have two tables named portfolio and portfolioplants. A one portfolio could be have multiple plants. When user create new portfolio with plants from API ı want to hold the portfolioID(PrimaryKey) and portfolio name in portfolio table, and hold the plants of portfolio's in portfolioplants table with portfolioID(ForeignKey) and plants of portfolio. What I'd like to do is, ı want to save portfolioID value (which is auto-incremented in portfolios table as a PK) in the portfolioID(FK) column of portfolioplants table automaticcly. So here is my sql querys:

app.post('/api/savePortfolio',(req,res)=>{
   const portfolioName = req.body.name
   const plants = req.body.plants
   const sqlInsertportfolio = "INSERT INTO portfolios (name) VALUES (?)"
   db.query(sqlInsertportfolio,[portfolioName],(err,result)=>{
       if(err) console.log(err)
       console.log(result)
       res.status(200)
   });

   const sqlInsertPortfoliofPlants = "INSERT INTO portfolioplants (plantName) VALUES (?)"
   plants.forEach(plant=>{
       db.query(sqlInsertPortfoliofPlants,[plant.name],(err,result)=>{
           if (err) console.log(err)
           console.log(result)
           res.status(200)
       })
   })
})

here is the portfolios table:

and here is the portfolioplants table:

I'd like the portfolioID column to be filled with the auto generated portfolioID value of its portfolio. THX in advance for your suggestions and have a healty day :))

score 0 · Answer 1 · answered Jul 28 '21 at 10:34

I have my solution like this way. Hope it helps to people who suffers with same issue with me :)

app.post('/api/savePortfolio',async (req,res)=>{
const portfolioName = req.body.name
const plants = req.body.plants

const sqlInsertportfolio = "INSERT INTO portfolios (name) VALUES (?)"
db.query(sqlInsertportfolio,[portfolioName],(err,result)=>{
    if(err) console.log(err)
    res.status(200)

    db.query('SELECT MAX(ID) as "portfolioID" from portfolios',(err,result)=>{
        if(err) console.log(err)
        var portfolioID = JSON.stringify(result[0].portfolioID)

        const sqlInsertPortfoliofPlants = "INSERT INTO portfolioplants (portfolioID,plantName) VALUES (?,?)"
        plants.forEach(plant=>{
            db.query(sqlInsertPortfoliofPlants,[portfolioID,plant.name],(err,result)=>{
                if (err) console.log(err)
                console.log(result)
                res.status(200)
            })
        })
    })
});})

This only works assuming there have been no other inserts. It is possible someone else could insert another row in between your inserts and you will pick up the wrong ID. Have a read of https://stackoverflow.com/questions/17112852/get-the-new-record-primary-key-id-from-mysql-insert-query — Jameson_uk, Jul 28 '21 at 10:45
Yes, you are absolutely right. I'll check this out. thx a lot :)) — alperenoz, Jul 28 '21 at 11:40

How can ı save automaticcly generated primary key as a foreign key in another table in MySQL?

1 Answers1