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@lfix.co.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

On Tue, Nov 01, 2005 at 02:22:07PM -0800, Bob Pawley wrote:

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

This sounds like the same thing you asked last week. Was the example
in the following message not helpful? Did I misunderstand what you're
trying to do?

http://archives.postgresql.org/pgsql-general/2005-10/msg01695.php

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.

Could you provide an example of what you're doing to get this error?

--
Michael Fuhr