Re: Modifying FK constraints

In order to find all FK declared on a table I query tg_trigger view. The
query lists all FKs declared on the table as well as all the ones
referencing the table. I noticed that FKs that are declared on the table
have pgtype equal to 21, and FKs referencing the table have pgtype 9 or
17.

The following query lists all the FKs declared table 'objective'. Is
this the right way to do this?

select t.tgconstrname, src.relname, dest.relname, t.tgtype
from pg_trigger t, pg_class src, pg_class dest
where t.tgrelid=src.oid
and t.tgconstrrelid=dest.oid
and t.tgisconstraint = 't'
and t.tgtype=21
and src.relname='objective';

Thanks.

Oleg


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Oleg Lebedev
Sent: Wednesday, November 10, 2004 10:37 AM
To: Richard Huxton
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Modifying FK constraints



Thanks, but the problem here is that I have multiple replicated
databases with the same schema and each of them contains numerous
tables. So, dropping and re-creating FK constraints manualy is not
feasible, and I am trying to automate this process.

Each table contains a primary key column "objectid", so this should make
it simpler to apply the same procedure to all tables.

So, what I want to do is:

FOR EACH TABLE
    1. Find out if there is an FK referencing its "objectid" column
    2. If there is one, then either drop and recreate the FK or
modify FKs system property to force cascading of updates. I would rather
prefer updating the system table.

So, I would need to know answers to the following questions:

1. How can I find out what FKs reference a the given column based on the
system table information? 2. What system table do I need to update to
force an FK constraint to cascade updates?

Thanks.

Oleg

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, November 10, 2004 10:23 AM
To: Oleg Lebedev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Modifying FK constraints


Oleg Lebedev wrote:
> Greetings.
>
> Is it possible to modify a foreign key constraint and force it to
> cascade on update? If there is no such SQL command, then is it
> possible to update some system tables to accomplish this?

BEGIN;
ALTER TABLE t1 DROP CONSTRAINT ...
ALTER TABLE t1 ADD CONSTRAINT ...
COMMIT;

Note that this will trigger a re-examination of all the values to check
the constraint is valid.

Also you may have to quote constraint names. If they are generated as $1

you will need to refer to them as "$1".

Full details in the "SQL Commands" chapter under "ALTER TABLE"
--
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly