pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign Key: what value?
4+ messages / 4 participants
[nested] [flat]
* Foreign Key: what value?
@ 2006-07-05 15:19 Davi Leal <davi@leals.com>
0 siblings, 2 replies; 4+ messages in thread
From: Davi Leal @ 2006-07-05 15:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Foreign Key: what value?
@ 2006-07-05 15:34 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Davi Leal <davi@leals.com>
1 sibling, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2006-07-05 15:34 UTC (permalink / raw)
To: pgsql-sql
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 ===
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Foreign Key: what value?
@ 2006-07-06 18:46 operationsengineer1@yahoo.com
parent: Davi Leal <davi@leals.com>
1 sibling, 1 reply; 4+ messages in thread
From: operationsengineer1@yahoo.com @ 2006-07-06 18:46 UTC (permalink / raw)
To: Davi Leal <davi@leals.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Foreign Key: what value?
@ 2006-07-06 19:32 Aaron Bono <postgresql@aranya.com>
parent: operationsengineer1@yahoo.com
0 siblings, 0 replies; 4+ messages in thread
From: Aaron Bono @ 2006-07-06 19:32 UTC (permalink / raw)
To: operationsengineer1@yahoo.com <operationsengineer1@yahoo.com>; +Cc: Davi Leal <davi@leals.com>; pgsql-sql
On 7/6/06, operationsengineer1@yahoo.com <operationsengineer1@yahoo.com>
wrote:
>
> 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.
Bigserial's are simply bigint's with a sequence that does the nextval part
for you. Your approach works but takes more coding on your part. I would
recommend using bigserial so you cut some of the work out for yourself.
-Aaron
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-07-06 19:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-05 15:19 Foreign Key: what value? Davi Leal <davi@leals.com>
2006-07-05 15:34 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-07-06 18:46 ` operationsengineer1@yahoo.com
2006-07-06 19:32 ` Aaron Bono <postgresql@aranya.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox