[solved] - insert multiple queries in a single step - retrieve and assing current id in many to many relationship

angelicaP

hi,

I use postgresql for database management.

i have three tables, all of them containing data already;
t1 contains "companies" owned by owners
t2 contains the 'owners' info
t3 contains the n-m relations: company - owner, because a company could have multiple owners and an owner could have multiple companies/shares in a company.

my challenge is to insert new companies in t1, to assign an owner from t2, the idcompany and the idowner to be inserted in t3 all in a single step

so, here is what i tried to do:
on a form, when OK pushbutton signal is launched the details of the company goes in t1 with the query: insert into company () values ();
the idcompany for the new company is obtained by following query:

select(Select max(idcompany) from company)+1; // syntax for postgresql

in the same form, i have a lineEdit where the owners options are revealed by QCompleter;

I would like to know the following:

as soon as I hit the OK button i get the following error;

insOwner error: "ERROR: insert or update on table "companyhasowner" violates foreign key constraint "fk_company_cohasowner"
DETAIL: Key (idcompany)=(51) is not present in table "company".
(23503) QPSQL: Unable to create query"

I'm thinking that i can't actually insert the idcompany and in the same time in t3, because the respective idcompany doesn't exist yet in t1.

So, is there a way to assign an owner to the company which i'm about to create in t1 and insert the current idcompany and idowner in t3 in the same shoot, when the info doesn't exists yet in t1?

if the above solution does not exists, would make sense, let's say to insert the info in t1 after all the details of the company (company name, address, phone) were typed, as soon as the returnPressed() signal is emitted? and then on a second query to retrieve the idcompany from t1 and assign it in t3?
is there another way to retrieve the idcompany from company table t2 expect what i'm using?
@intcompany.exec("select ((Select max(idcompany) from company) + 1);");@

what do you think? thank you for your inputs.
i'm sure it's nothing complicated, but i'm not sure how to implement it correctly.

here is my code, in case the above explanation is not complete:

@void newcompany::on_pushButton_clicked()
{
QSqlDatabase e = QSqlDatabase::database("connDb");
QSqlQuery insNewco(e), insOwner(e), textowner(e), intcompany(e);
if (e.open())
{
insNewco.prepare("INSERT INTO company (companyname, address, phone) VALUES (:companyname, :address, :phone)");
insNewco.bindValue(":companyname", ui->lEco->text());
insNewco.bindValue(":address", ui->lEcoaddress->text());
insNewco.bindValue(":phone", ui->lEcophone->text());

   if (insNewco.exec&#40;&#41;&#41;
   {
       qDebug() << "insNewco executed";
   }
   else
   {
       qDebug() << "insNewco error: " << insNewCO.lastError().text();
   };

   textowner.prepare("select idowner from owner where owner=:lneditownertext"); 
   textowner.bindValue(":lneditownertext" ,ui->ln_existinOwners->text()); //in ln_existinOwners is the owner name returned using the QCompleter
   textowner.exec&#40;&#41;;
   intcompany.exec("select ((Select max(idcompany&#41; from company&#41; + 1);");

   insOwner.prepare("INSERT INTO cohasowner (idcompany, idowner) VALUES (:idcompany, :idowner)");
   while (intcompany.next()) 
   {
   QString returnedId = intcompany.value(0).toString();
   insOwner.bindValue(":idcompany", returnedId ); 
   }

   while (textowner.next()) 
   {
   QString returnedId = textowner.value(0).toString();
   insOwner.bindValue(":idowner", returnedId);
   } 
       if (insOwner.exec&#40;&#41;&#41;  
       {
           qDebug() << "insOnwer executed";
       }
       else
       {
           qDebug() << "insOwner error: " << insOwner.lastError().text();
       };

   }else
        {
           qDebug() << "connexion error:" << e.lastError().text();
        };

e.close();
close();
}@

note:
idcompany, idowner are in postgresql type 'serial'

angelicaP

i'm not sure if my post was too long or not interesting, but I would like to know some possible approaches

option A: ...
option B: ...

what worked for you in the above problem. this kind of inputs are not available in documentation. or, i don't know where to find it?

panosk

Hi,

Here are some ideas:

Make sure your database schema and the constrains are correct. You can check this by manually executing the queries you want directly to the database.
If the above is fine, then break your function into smaller ones, each executing a single query, and chain them in the correct order so the constrains are always met.
Depending on how you have set your schema and your constrains, you may be able to create a trigger in postgre to populate table3 instead of using a query.

I hope this helps a little.

angelicaP

hi,

as soon as I editFinished() the values from form newcompany go into my table and foreigh key violation disappear.

I didn't try the trigger option, but I will do it in my next exercise.