Re: Cascading Changes

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