for what it's worth...i wrote this perl script to manage
deleting foreign keys and keeping the pg_class table in
synch with pg_trigger. it will do local hosts or remote
hosts. like most free things it probably can be improved
on.
please see attached...
HTH somewhat,
mikeo
At 11:38 AM 8/7/00 -0700, Stephan Szabo wrote:
>
>Umm, it's very hard to do automatically. Pretty much, your best bet is to
>look at pg_trigger and find constraints that reference the tables you're
>doing, dumping the table schema, dropping the table, removing the create
>constraint trigger statements that are dumped and replacing them with an
>ALTER TABLE ADD CONSTRAINT.
>
>
>> How can I manipulate existing unnamed (created automaticly by foreign key)
>> constraints on tables in PSQL tool ?
>Don't use unnamed constraints? :-)
>In practice it's probably always good form to use:
>constraint <name> references ... or
>constraint <name> foreign key (...) references ...
>
>Seriously, for foreign key constraints, you can remove them by removing
>the rows in pg_trigger that are associated with them. If they're unnamed,
>you'll have to use the data in tgargs to determine which is the correct
>one.
>
>---
>Reading pg_trigger for fk constraints
>
>The function that is referenced will tell you which trigger it is...
>You'll need to do something like
>select pg_trigger.*, proname from pg_trigger, pg_proc where
>pg_trigger.tgfoid=pg_proc.oid.
>And the proname will be like
>RI_<thing to do>_<ins|del|upd>
>The checking constraint on the referencing table is
>RI_check_ins I believe. The constraints on the referenced table will
>have the action you specified in the name, so RI_cascade_upd or
>RI_setnull_del.
>
>Tgargs stores the information on the tables and columns referenced.
>It's in the form:
>name\000referencing table\000referenced table\000match type\000
>referencing column1\000referenced column 1\000...
>(Note: the internal form here may change for 7.1)
>
>
