Thread: Cascading Changes

Hi,

I have tried looking for cascading changes through multiple tables and don't
seem to be able to find anything in the documentation.  Can anybody point me
in the right direction.

I just want to change the primary key in two linked records in two different
tables from 'Fox' to 'FOX'

Thanks

Tom

What do you mean by "linked"? Do you mean that there is referential
integrity (foriegn key constraint) between the two? Or do you just mean that
they contain the same value, and this is a understood-by-you logical link?

If there is referential integrity, you should define the RI as "ON UPDATE
CASCADE", and changes made to the parent table key will roll out to the
child table.

If there is no RI, then just write two UPDATE statements.

If there is RI, but you didn't define ON UPDATE CASCADE (the default is to
forbid changing a parent or pkey is a child refers to it), then you'll need
to DROP the constraint TRIGGER, run two separate UPDATE statements, and
re-create the trigger.

(Or, more sneaky but faster, you can hack a bit in the system tables to turn
the constraint to ON UPDATE CASCADE. See my article at
techdocs.postgresql.org on "Hacking the RI Tables" for more information.)

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Ansley
> Sent: Tuesday, May 14, 2002 12:09 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Cascading Changes
>
>
> Hi,
>
> I have tried looking for cascading changes through multiple
> tables and don't
> seem to be able to find anything in the documentation.  Can
> anybody point me
> in the right direction.
>
> I just want to change the primary key in two linked records in
> two different
> tables from 'Fox' to 'FOX'
>
> Thanks
>
> Tom
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

On Tue, 2002-05-14 at 17:08, Tom Ansley wrote:
> Hi,
>
> I have tried looking for cascading changes through multiple tables and don't
> seem to be able to find anything in the documentation.  Can anybody point me
> in the right direction.
>
> I just want to change the primary key in two linked records in two different
> tables from 'Fox' to 'FOX'
>

This will happen automatically if one table makes a foreign key
reference to the other:

CREATE TABLE t1 (id    TEXT     PRIMARY KEY,
                 ...
                );

CREATE TABLE t2 (id    TEXT     PRIMARY KEY
                                REFERENCES t1 (id)
                                   ON UPDATE CASCADE
                                   ON DELETE RESTRICT,
                 ...
                );

Now any entry in t2.id must match a primary key in t1.id.  Whenever such
a key is changed, it is also changed in t2.id.  A row in t1 cannot be
deleted if it is referenced in t2.id.

If a foreign key relationship is not appropriate (t2.id may contain
something that is not a key in t1) you can use triggers to do whatever
you want, but you will have to program it all.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "Yea, though I walk through the valley of the shadow of
      death, I will fear no evil, for thou art with me;
      thy rod and thy staff they comfort me."  Psalms 23:4