Postgres Pro
Downloads
Home   >   mailing lists

Thread: Linking

Linking

From
Bob Pawley
Date:

I have created the following tables.

 

First Table -

 

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)

);

 

---------

 Second Table -

 

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.

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.

 

What do I need to do?

 Bob

Re: Linking

From
Oliver Elphick
Date:
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

Re: Linking

From
Michael Fuhr
Date:
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