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(dot)Elphick(at)lfix(dot)co(dot)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