Обсуждение: 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

On Wed, Nov 10, 2004 at 10:00:02AM -0700, Oleg Lebedev wrote:

> 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?

You can drop and add constraints with ALTER TABLE.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

On Wed, 10 Nov 2004, Oleg Lebedev wrote:

> 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?
>
> The problem is that I have a bunch of tables with FK constraints and I
> need to update primary key values in a lot of these tables. FK
> constraints were declared, but without cascading updates. Now, I am
> trying to modify all of them to cascade updates so that I can change
> primary keys and have these changes propaged to the referencing values.
>
> What would be the easiest solution for this?

The easiest solution is to drop the constraint and re-add it with the
changed parameters.  This will however check the constraint against the
current table data.

It would probably be possible to change the behavior by updating the
appropriate rows in the system tables.  You would need at least to change
tgfoid in pg_trigger for the after update trigger on the referenced table.

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

On Wed, 10 Nov 2004, Oleg Lebedev wrote:

>
> 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';

I think that should work as long as you aren't going around making your
own constraint triggers.

I believe you can get info from pg_constraint as well in recent versions,
maybe something like:

select pg_constraint.conname, c.relname, c2.relname from
pg_constraint,pg_class c, pg_class c2 where contype='f' and conrelid=c.oid
and confrelid=c2.oid and c.relname='objective';

You can also get some other information that's hard to get from the
triggers like the referential actions (pg_constraint.confupdtype and
confdeltype).