postgresql - How do I disable referential integrity in Postgres 8.2?

Question

Google results on this one are a bit thin, but suggest that it is not easily possible.

My specific problem is that I need to renumber the IDs in two tables that are related to each other such that table B has an "table_a_id" column in it. I can't renumber table A first because then its children in B point to the old IDs. I can't renumber table B first because then they would point to the new IDs before they were created. Now repeat for three or four tables.

I don't really want to have to fiddle around with individual relationships when I could just "start transaction; disable ref integrity; sort IDs out; re-enable ref integrity; commit transaction". Mysql and MSSQL both provide this functionality IIRC so I would be surprised if Postgres didn't.

Thanks!

Answer 1

There are two things you can do (these are complementary, not alternatives):

• Create your foreign key constraints as DEFERRABLE. Then, call "SET CONSTRAINTS DEFERRED;", which will cause foreign key constraints not to be checked until the end of the transaction. Note that the default if you don't specify anything is NOT DEFERRABLE (annoyingly).
• Call "ALTER TABLE mytable DISABLE TRIGGER ALL;", which prevents any triggers executing while you load data, then "ALTER TABLE mytable ENABLE TRIGGER ALL;" when you're done to re-enable them.