By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,811 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,811 IT Pros & Developers. It's quick & easy.

Modifying FK constraints

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


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

Nov 23 '05 #2

P: n/a
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/

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

http://archives.postgresql.org

Nov 23 '05 #3

P: n/a
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.

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

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.