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

Re: Help with transactions and FK's

From: "Michael Schmidt" <michaelmschmidt(at)msn(dot)com>
To: "Dave Cramer" <pg(at)fastcrypt(dot)com>
Cc: "PostgreSQL JDBC" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Help with transactions and FK's
Date: 2006-11-13 18:48:36
Message-ID: BAY114-DAV46FC673671302BAE42033A3F40@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-jdbc
Mr. Cramer,
Thanks for the very quick reply.  Given the sophistication of PostgreSQL, I had hoped it would be that simple!
Michael Schmidt
  ----- Original Message ----- 
  From: Dave Cramer<mailto:pg(at)fastcrypt(dot)com> 
  To: Michael Schmidt<mailto:michaelmschmidt(at)msn(dot)com> 
  Cc: PostgreSQL JDBC<mailto:pgsql-jdbc(at)postgresql(dot)org> 
  Sent: Monday, November 13, 2006 10:24 AM
  Subject: Re: [JDBC] 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



In response to

pgsql-jdbc by date

Next:From: surabhi.ahujaDate: 2006-11-14 08:27:20
Subject: JDBC driver for PostgreSQL 8.1.5
Previous:From: Dave CramerDate: 2006-11-13 17:24:48
Subject: Re: Help with transactions and FK's

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