Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Norman Khine <norman@spot5.com>, <pgsql-sql@postgresql.org>
Subject: Re: Multi table insert and passing sequence ids
Date: Tue, 6 Aug 2002 18:38:23 +0100
User-Agent: KMail/1.4.1
References: <20020806123829.C1040-100000@reaper.spot5.com>
In-Reply-To: <20020806123829.C1040-100000@reaper.spot5.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200208061838.23195.dev@archonet.com>

On Tuesday 06 Aug 2002 1:55 pm, Norman Khine wrote:
> Hello,
> I have a database that has 4 tables

> I also have an html put form which is an almagamation of all the fields
> from these tables and am trying to write the sql which will allow me to
> put this data into the database, in particular I am unsure as to howto
> pass the sequence id from business_name to the business_address_1 FOREIGN
> KEY, perhaps I'll need to do it using 4 different sql statements?

Norman - see the thread "problem fetching currval of sequence" for someone=
=20
doing just this.

You will need 4 statements, but I don't see the sequence definition for=20
business_name

> CREATE TABLE business_name(
> business_name_id int4 NOT NULL CONSTRAINT UC_business_name1 UNIQUE,
> business_name varchar(40),
> business_url varchar(35),
> CONSTRAINT PK_business_name1 PRIMARY KEY (business_name_id));

You'll want a "DEFAULT nextval('business_name_id_seq')" (or whatever you ca=
ll=20
the sequence) on business_name_id . Then you can do:

BEGIN;
INSERT INTO busines_name (business_name,business_url) VALUES (...);
INSERT INTO county (business_name_id,...) VALUES=20
(currval('business_name_id_seq',...)
etc...
COMMIT;

Wrapping the statements in begin/commit ensure they all take place in one=
=20
transaction and so either all happen or none do. The sequence value is=20
guaranteed to be unique for the current process, so two people can insert=
=20
entries at the same time.

If you didn't want to do the DEFAULT bit with business_name.business_name_i=
d=20
(and I'd recommend you do) then you'd use nextval('business_name_id_seq') t=
o=20
get the new id to use. Check the docs on currval/nextval/create sequence fo=
r=20
details.

HTH

- Richard Huxton