Re: Help with transactions and FK's

Michael,

It will work as you expect.

You actually don't even have to get it back, as long as you use the
same connection you can do

assuming the name of the sequence for parent record is parent_id_seq

insert into parent record

insert into child (child_id,.....) values (currval
('parent_id_seq'),.....)

Dave
On 13-Nov-06, at 12:05 PM, Michael Schmidt wrote:

> Folks,
> I'm sure this is a basic question, but after reading the PG
> documentation and some posts on the lists, I still haven't found a
> solution. So, I hope someone can provide an answer without too
> much effort.
>
> I have a scientific bibliography database in which a "data item"
> consists of a parent record with several child and grandchild
> records, linked with foreign keys. Using my Java front-end and
> JDBC, I want to insert a new "data item" via a transaction. My
> thought is to do the following steps:
>
> SET TRANSACTION
> insert the parent record
> get the parent record primary key with currval() of the primary key
> sequence
> set the child record foreign key value to the value from currval()
> insert the child records
> COMMIT
> roll back on error
>
> for the grandchild records, several currval() calls will be
> needed. My question is - will currval() provide the correct
> primary key value for the records inserted in the transaction, or
> will it provide the value of the sequence at the beginning of the
> transaction? If the latter, how to proceed?
>
> Thanks for your time.
> Michael Schmidt
>