Re: Multi table insert and passing sequence ids

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
doing just this.

You will need 4 statements, but I don't see the sequence definition for
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 call
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
(currval('business_name_id_seq',...)
etc...
COMMIT;

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

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

HTH

- Richard Huxton