Обсуждение: Foreign Key: what value?

Hi,
How to know the value which I must set in the foreign key field?. I have two 
tables:


CREATE TABLE AAAAAA ( Id  SERIAL PRIMARY KEY, data char(9)
);

CREATE TABLE BBBBBB ( BBBBBB_Id integer REFERENCES AAAAAA(Id) NOT NULL, field char(5)
);




I insert a register on table AAAAAA,
  INSERT INTO AAAAAA (data) VALUES ('123456789');


and then, I want to insert a related register in table BBBBBB, but I do not
know how get the proper value to the Foreign key BBBBBB_Id. Note that a lot of 
client are inserting at the same time on the AAAAAA table, so I can not just 
get the greater value of AAAAAA.Id

Maybe using transactions ?. Any tip, URI, ... will be welcome.
  INSERT INTO BBBBBB (BBBBBB_Id, field) VALUES (??????,'12345');


Regards,
Davi

am  05.07.2006, um 17:19:26 +0200 mailte Davi Leal folgendes:
> Hi,
> How to know the value which I must set in the foreign key field?. I have two 
> tables:
> 
> 
> CREATE TABLE AAAAAA (
>   Id  SERIAL PRIMARY KEY,
>   data char(9)
> );
> 
> CREATE TABLE BBBBBB (
>   BBBBBB_Id integer REFERENCES AAAAAA(Id) NOT NULL,
>   field char(5)
> );
> 
> 
> 
> 
> I insert a register on table AAAAAA,
> 
>    INSERT INTO AAAAAA (data) VALUES ('123456789');
> 
> 
> and then, I want to insert a related register in table BBBBBB, but I do not
> know how get the proper value to the Foreign key BBBBBB_Id. Note that a lot of 

currval() is your friend:
17:33 < akretschmer> ??currval
17:33 < pg_docbot_ads> For information about 'currval' see:
17:33 < pg_docbot_ads> http://www.postgresql.org/docs/current/static/functions-sequence.html


HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 

A. Kretschmer wrote:
> Davi Leal folgendes:
> > Hi,
> > How to know the value which I must set in the foreign key field?. I have
> > two tables:
> >
> >
> > CREATE TABLE AAAAAA (
> >   Id  SERIAL PRIMARY KEY,
> >   data char(9)
> > );
> >
> > CREATE TABLE BBBBBB (
> >   BBBBBB_Id integer REFERENCES AAAAAA(Id) NOT NULL,
> >   field char(5)
> > );
> >
> >
> >
> >
> > I insert a register on table AAAAAA,
> >
> >    INSERT INTO AAAAAA (data) VALUES ('123456789');
> >
> >
> > and then, I want to insert a related register in table BBBBBB, but I do
> > not know how get the proper value to the Foreign key BBBBBB_Id. Note that
> > a lot of
>
> currval() is your friend:
> 17:33 < akretschmer> ??currval
> 17:33 < pg_docbot_ads> For information about 'currval' see:
> 17:33 < pg_docbot_ads>
> http://www.postgresql.org/docs/current/static/functions-sequence.html

It works.
Thanks Andreas

> Hi,
> How to know the value which I must set in the
> foreign key field?. I have two 
> tables:
> 
> 
> CREATE TABLE AAAAAA (
>   Id  SERIAL PRIMARY KEY,
>   data char(9)
> );
> 
> CREATE TABLE BBBBBB (
>   BBBBBB_Id integer REFERENCES AAAAAA(Id) NOT NULL,
>   field char(5)
> );
> 
> 
> 
> 
> I insert a register on table AAAAAA,
> 
>    INSERT INTO AAAAAA (data) VALUES ('123456789');
> 
> 
> and then, I want to insert a related register in
> table BBBBBB, but I do not
> know how get the proper value to the Foreign key
> BBBBBB_Id. Note that a lot of 
> client are inserting at the same time on the AAAAAA
> table, so I can not just 
> get the greater value of AAAAAA.Id
> 
> Maybe using transactions ?. Any tip, URI, ... will
> be welcome.
> 
>    INSERT INTO BBBBBB (BBBBBB_Id, field) VALUES
> (??????,'12345');
> 

i alsways make my foreign key column data type int4. 
i'm not sure if i read that somewhere.  anyone, please
feel free to chime in if this isn't good practice.

read up on currval, nextval and that whole section. 
you can begin by getting the nextval, assigning it to
a variable, insert it into your primary table and then
insert it into your related table as a foreign key.

from what i understand, either way should be
bulletproof.  the way i described is more code, but
some minds might not mind paying that price b/c they
like the process better.

good luck.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com