Re: 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(at)archonet(dot)com]
Sent: Wednesday, November 10, 2004 10:23 AM
To: Oleg Lebedev
Cc: pgsql-general(at)postgresql(dot)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