Modifying FK constraints

[Oleg Lebedev]

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?

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?

Thanks.

Oleg

[Richard Huxton]

Re: Modifying FK constraints

Oleg Lebedev wrote:[color=blue]
> 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?[/color]

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 4: Don't 'kill -9' the postmaster

[Michael Fuhr]

Re: Modifying FK constraints

On Wed, Nov 10, 2004 at 10:00:02AM -0700, Oleg Lebedev wrote:
[color=blue]
> 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?[/color]

You can drop and add constraints with ALTER TABLE.

--
Michael Fuhr

http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Stephan Szabo]

Re: Modifying FK constraints

On Wed, 10 Nov 2004, Oleg Lebedev wrote:
[color=blue]
> 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?[/color]

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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings