From: | mikeo <mikeo(at)spectrumtelecorp(dot)com> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>, radek(dot)stachowiak(at)alter(dot)pl |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: foreign keys |
Date: | 2000-08-07 20:15:27 |
Message-ID: | 3.0.1.32.20000807161527.00ad6d90@pop.spectrumtelecorp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
>
>
Attachment | Content-Type | Size |
---|---|---|
delete_some_fkeys.pl | text/plain | 6.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Morten W. Petersen | 2000-08-07 20:35:12 | Re: |
Previous Message | Tom Lane | 2000-08-07 18:57:39 | Re: [HACKERS] Re: Trouble with float4 afterupgrading from 6.5.3 to 7.0.2 |