Thread: Multi table insert and passing sequence ids

Hello,
I have a database that has 4 tables

Reseller
BusinessName
Address
County

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?

Any advice will be very much appreciated

Tahnks

Norman


###########################################################################

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));

COMMENT ON TABLE business_name IS 'This is the name of the business.';
COMMENT ON COLUMN business_name.business_url IS 'URL address for
Business';

CREATE TABLE county(
county_id int4 NOT NULL CONSTRAINT UC_county1 UNIQUE,
county varchar(40),
region_id int4 NOT NULL,
CONSTRAINT county_1 FOREIGN KEY (region_id) REFERENCES region (region_id),
CONSTRAINT PK_county1 PRIMARY KEY (county_id));

COMMENT ON TABLE county IS 'County where the the business address is based
from';

CREATE TABLE business_address(
business_address_id int4 NOT NULL CONSTRAINT UC_business_address1 UNIQUE,
street_name varchar(25),
town varchar(25),
county varchar(25),
postcode varchar(10),
county_id int4 NOT NULL,
business_name_id int4 NOT NULL,
CONSTRAINT business_address_1 FOREIGN KEY (business_name_id) REFERENCES
business_name (business_name_id),
CONSTRAINT business_address_2 FOREIGN KEY (county_id) REFERENCES county
(county_id),
CONSTRAINT PK_business_address1 PRIMARY KEY (business_address_id));

COMMENT ON TABLE business_address IS 'Full postal address of business';

CREATE TABLE reseller(
reseller_id int4 NOT NULL CONSTRAINT UC_reseller1 UNIQUE,
title char(10),
initials char(5),
surname char(35),
email varchar(45),
username varchar(10),
password varchar(10),
regdate timestamp,
status bool,
business_name_id int4 NOT NULL,
group_id int4 NOT NULL,
CONSTRAINT reseller_1 FOREIGN KEY (business_name_id) REFERENCES
business_name (business_name_id),
CONSTRAINT reseller_2 FOREIGN KEY (group_id) REFERENCES groups (group_id),
CONSTRAINT PK_reseller1 PRIMARY KEY (reseller_id));

COMMENT ON TABLE reseller IS 'This is the actual user / reseller.';
COMMENT ON COLUMN reseller.regdate IS 'Every time the person updates the
entry, this field is updated.';
COMMENT ON COLUMN reseller.status IS 'Tells us if the Reseller is active
or not';
CREATE INDEX IDX_Person ON reseller (username,surname);


######################################################################

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