Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id D6A1C474E61 for ; Wed, 13 Mar 2002 01:57:03 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id AA581D5FA; Tue, 12 Mar 2002 22:57:03 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 9FDBF5BFD; Tue, 12 Mar 2002 22:57:03 -0800 (PST) Date: Tue, 12 Mar 2002 22:57:03 -0800 (PST) From: Stephan Szabo To: Dino Cherian K Cc: Subject: Re: [INIMSS] How to foreign key In-Reply-To: <02031311263300.01381@dino> Message-ID: <20020312225002.N76430-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200203/129 X-Sequence-Number: 7009 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.