Re: Linking

On Tue, 2005-11-01 at 14:22 -0800, Bob Pawley wrote:
> I have created the following tables.

> CREATE TABLE process
> (
> process_name varchar(60) NOT NULL,
> fluid_id serial NOT NULL,
> fluid varchar(30) NOT NULL,
> ip_op_reactor varchar(3),
> source varchar(30),
> destination varchar(30),
> CONSTRAINT process_pk PRIMARY KEY (fluid_id)
> );

> CREATE TABLE specification
> (
> fluid_id int4 NOT NULL,
> line_vessel_ident varchar(30),
> CONSTRAINT specification_pk PRIMARY KEY (fluid_id),
> CONSTRAINT specification_fluid_id_fk FOREIGN KEY (fluid_id)
> REFERENCES process (fluid_id)
> );

> On data entry I want the serial number generated in process.fluid_id
> to be transferred to column specification.fluid_id.

You need a trigger function called by a trigger.

CREATE OR REPLACE FUNCTION process_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO specification (fluid_id) VALUES (NEW.fluid_id);
END IF;
RETURN NULL;
END;$$;

CREATE TRIGGER process_fluid_id AFTER INSERT ON process
FOR EACH ROW EXECUTE PROCEDURE process_trigger();

> A simple insert/select command will transfer the serial number but
> entering a second row then employing an insert/select command violates
> the p_k unique rule.

I don't understand what you're saying at here.

You probably want to add ON UPDATE CASCADE to the foreign key on
specification.fluid_id, if updates are allowed to change the value of
process.fluid_id. Similarly, if process records can be deleted, you
probably need to specify ON DELETE CASCADE. Maybe too you want to add a
reverse foreign key on process.fluid_id; if so it would have to be
DEFERRABLE, so that the trigger could insert the records without causing
errors.

--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html