Re: Modifying FK constraints

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.