Redefining foreign keys, much easier than I thought (Accessing database over ssh w/ 10 network hops is *very* slow...)

For those who are interested in how to alter the ON DELETE/ON CASCADE features of a foreign key constraint. There's no need whatsoever to alter the system catalogs directly, I'd just missed the ALTER TABLE variant that drops constraints, namely "DROP CONSTRAINT".

Turns out that the drop commands are using the pg_depend table to decide how to cascade the drops to the trigger table, and without that logic you just royally mess up when you start deleting things from pg_constraint manually.

Anyway, for those who might need to do such a thing yourselves, here's a script that takes a PyTable schema and traverses the database updating all foreign-key references to match those in the schema.


Comments are closed.


Pingbacks are closed.