Inserting with Foreign Key

From: Yanick Hudon <yannik_at_sympatico.ca>
Date: Wed, 5 Dec 2001 20:39:21 -0800
Message-ID: <S4AP7.34158$yE5.2263340@news20.bellglobal.com>

Hi all,

    I've got some problems inserting records with a scripts in SQL.

The following code runs OK because composanteID is a primary Key and categorieID is a foreign Key:

 insert into composantes
   ( composanteid, categorieid,

     codelieux, nom, description,
     quantitestock, minimumstock

   )
(
Select NewIdPcs,

       RefIdCat,
       'A3#1',
       'composante21',
       'descrpcs21',
       100,
       25

From
  (
    select max(composanteid) + 1 NewIDPcs     from composantes
) AA,

  ( Select max(categorieid) + 1 RefIdCat

     from categories
) RefCategories

);

commit;

Here's should be the same logic to insert the records with 2 foreign keys:

insert into composantesprojets

   ( composanteid, projetid,
     qterequise, qtebrisee, qtedefectueuse    )
(
Select PcsPrj,

       RefPrj,
       5,
       0,
       0

From
  (
    select min(composanteid) PcsPrj
    from composantes
) AA,

  ( Select min(Projetid) RefPrj

     from projets
) RefProjet

);

Here's the select that's running OK:

SQL> Select PcsPrj,

  2         RefPrj,
  3         5,
  4         0,
  5         0

  6 From
  7 (

  8      select min(composanteid) PcsPrj
  9      from composantes

 10 ) AA,
 11 ( Select min(Projetid) RefPrj
 12 from projets
 13 ) RefProjet
 14 /

    PCSPRJ REFPRJ 5 0 0 ---------- ---------- ---------- ---------- ----------

         1 106 5 0 0

I want to include this record in a table

Here's the code for inserting:

SQL> insert into composantesprojets

  2     ( composanteid, projetid,
  3       qterequise, qtebrisee, qtedefectueuse
  4     )

  5 (
  6 Select PcsPrj,

  7         RefPrj,
  8         5,
  9         0,
 10         0

 11 From
 12 (

 13      select min(composanteid) PcsPrj
 14      from composantes

 15 ) AA,
 16 ( Select min(Projetid) RefPrj
 17 from projets
 18 ) RefProjet
 19 );
insert into composantesprojets
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.C_PCS_PRJ_FK_NOPCS) violated - parent
key not found

Any clues??

Thanks in advance! Received on Wed Dec 05 2001 - 22:39:21 CST