On Wed, 13 Mar 2002, Dino Cherian K wrote:
> Hi
>
> Is there any way that I can manage the relation between two tables in some
> what automatic way?
>
> I have two tables A and B with primary keys pka and pkb respectively, of type
> serial. the pka is refered in the table B as foreign key. Its somewhat like
> the one given below.
>
> Table A
> -------------
> pka | serial
> name | varchar(20)
> status | int4
>
> Table B
> -------------
> pka | serial
> pkb | int4
> child | varchar(20)
> status | int4
>
> Now the operator enters information to both the table from the same GUI
> window, so that the data relevant to table A is inserted and we take the
> newly got pka value by selecting ths MAX(A.pka). Then we insert the data
> relevant to the table B along with the new pka value. Here both the tables
> are exclusively locked before starting the data insertions, and later
> unlocked.
>
> This was the method I used in my program. Is there any other method by which
> we can make it in a better way?
Well, B.pka should probably just be an int, and you don't need to use
max() to get the last sequence value given to A in your session
currval is probably better (in part because you'd have the option
of not locking the tables exclusively and bin part because that gets rid
of the possibly expensive select MAX() ). Check the documentation on
sequences for more information.