Skip site navigation (1) Skip section navigation (2)

problem with foreign key

From: "pie yves" <yvespiel(at)hotmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: problem with foreign key
Date: 2001-09-12 10:28:19
Message-ID: F76lNYSqMN5IFxhcV9U0000da4a@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
hello,
I have a problem with a foreign key.
I have got a 7.1 PGSQL and pg_dump version


I've got a foreign key error whereas i'm sure that the value exists since i 
make a SELECT before.
It's a PHP/PGSQL application. There is what it returns :
(it's what i have into my HTML page)

---------------------------------------------------------------------
the parameters are : name : the establishment and the city : the city
the test request :: select * from etablissement where E_libelle='the 
establishment' AND E_Localite='the city'
error of the test request ::
the result of the test :: the name : the establishment -- where is it : the 
city -- the structure : the struct

requete SQL :: INSERT INTO RDipEta VALUES (874, 855, 855, 'A', 'dut', 'the 
establishment', 'the city', TRUE, TRUE, TRUE, TRUE, TRUE,
TRUE, TRUE, '');

error :: ERROR: fkrde_etablissementkey referential integrity violation - key 
referenced from rdipeta not found in etablissement
----------------------------------------------------------------------


The test request make me being sure that the establishment exists since i 
use the same parameters. So why is there a foreign key error ?

Here is my PHP script :



----------------------------------------------------------------------
function insertRelationDiplomeEtablissement($Rde_NivCod, $Rde_GroupCod, 
$Rde_ForNum, $Rde_LettreCod, $Rde_TypeForCod, $Rde_Mot, $Rde_Localite, 
$Rde_FInitiale, $Rde_FContinue, $Rde_TPlein, $Rde_Correspondance, 
$Rde_Apprentissage, $Rde_Alternance, $Rde_Qualification, $Rde_Autre) {
  GLOBAL $DB, $q;
  print("the parameters are : name : $Rde_Mot and the city : $Rde_Localite 
<BR>");
  $sqlTst="select * from etablissement where E_libelle='$Rde_Mot' AND 
E_Localite='$Rde_Localite'";
  print("the test request :: $sqlTst <BR>");
  $res = new query($DB, $sqlTst);
  print("error of the test request :: ".$res->error()."<BR>");
  $row = $res->getrow();
  print("the result of the test :: the name : $row[0] -- where is it : 
$row[1] -- the structure : $row[2] <BR>");

  $sSQL = "INSERT INTO RDipEta VALUES ($Rde_NivCod, $Rde_GroupCod, 
$Rde_ForNum, '$Rde_LettreCod', '$Rde_TypeForCod', '$Rde_Mot', 
'$Rde_Localite', $Rde_FInitiale, $Rde_FContinue, $Rde_TPlein, 
$Rde_Correspondance, $Rde_Apprentissage, $Rde_Alternance, 
$Rde_Qualification, '$Rde_Autre');";
        print("requete SQL :: $sSQL <BR><BR>");
  $q = new query($DB, $sSQL);
        print("error :: ".$q->error()."<BR>");
  return $q;
}
----------------------------------------------------------------------

Moreover, there is values without errors ...

---------------------------------------------------------------------

the parameters are : name : my school and the city : the city
the test request :: select * from etablissement where E_libelle='my school' 
AND E_Localite='the city'
error of the test request ::
the result of the test :: the name : my school -- where is it : the city -- 
the structure : the struct

requete SQL :: INSERT INTO RDipEta VALUES (874, 855, 855, 'A', 'dut', 'my 
school', 'the city', TRUE, TRUE, TRUE, TRUE, TRUE, TRUE,
TRUE, '');

error ::

--------------------------------------------------------------------

And then the relation diplome with establishment (RDipEta) is good inserted
All the values have been inserted throught a HTML formular (so i think it's 
not a special caracter error)

there is the creation of the trigger script :

---------------------------------------------------------------------

CREATE CONSTRAINT TRIGGER "fkrde_etablissementkey" AFTER INSERT OR UPDATE ON 
"rdipeta"  NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE 
"RI_FKey_check_ins" ('fkrde_etablissementkey', 'rdipeta', 'etablissement', 
'UNSPECIFIED', 'rde_libelle', 'e_libelle', 'rde_localite', 'e_localite');


---------------------------------------------------------------------

It's pg_dump which have generated that script and i think it's good too.

I don't understand and i think it's a pgsql bug since the tuple is found 
with the SELECT query and not with the foreign key.
If you think it's me who make the mistake mail me please.....i'm becoming 
mad :)
If you find it strange and you want more explanation mail me too
thank you


yves piel
yvespiel(at)hotmail(dot)com
france

_________________________________________________________________
Téléchargez MSN Explorer gratuitement à l'adresse 
http://explorer.msn.fr/intl.asp


pgsql-bugs by date

Next:From: Tomasz MyrtaDate: 2001-09-12 12:22:18
Subject: dynamic-static date
Previous:From: Angus LeesDate: 2001-09-12 08:08:15
Subject: PQexec infinite loop

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group