Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I disable referential integrity in Postgres 8.2?

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!

like image 454
sanbikinoraion Avatar asked Sep 26 '08 14:09

sanbikinoraion


People also ask

What is referential integrity in PostgreSQL?

Referential integrity is the feature of a database ensuring implied relationships in the database are enforced. It is a feature of most database systems, and protects users from accidentally (or intentionally!) creating discrepencies in their database.


1 Answers

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.
like image 76
Nick Johnson Avatar answered Sep 21 '22 09:09

Nick Johnson