How do I disable referential integrity in Postgres 8.2?

Question

Hi!

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

It does not seem possible. Other suggestions almost always refer to dropping the constraints and recreating them after work is done.

However, it seems you can make constraints DEFERRABLE, such that they are not checked until the end of a transaction. See PostgreSQL documentation for CREATE TABLE (search for 'deferrable', it's in the middle of the page).

Answer 2

I think you need to make a list of your foreign key constraints, drop them, do your changes, then add the constraints again. Check the documentation for alter table drop constraint and alter table add constraint.

Answer 3

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 TRIGGERS;", which prevents any triggers executing while you load data, then "ALTER TABLE mytable ENABLE TRIGGERS;" when you're done to re-enable them.

Answer 4

Here's a script that will delete all constraints in a transaction, run some queries, then recreate all those constraints. pg_get_constraintdef makes this super-easy:

class no_constraints(object):
    def __init__(self, connection):
        self.connection = connection

    def __enter__(self):
        self.transaction = self.connection.begin()
        try:
            self._drop_constraints()
        except:
            self.transaction.rollback()
            raise

    def __exit__(self, exc_type, exc_value, traceback):
        if exc_type is not None:
            self.transaction.rollback()
        else:
            try:
                self._create_constraints()
                self.transaction.commit()
            except:
                self.transaction.rollback()
                raise

    def _drop_constraints(self):
        self._constraints = self._all_constraints()

        for tablename, name, def_ in self._constraints:
            self.connection.execute('ALTER TABLE "%s" DROP CONSTRAINT %s' % (tablename, name))

    def _create_constraints(self):
        for tablename, name, def_ in self._constraints:
            self.connection.execute('ALTER TABLE "%s" ADD CONSTRAINT %s %s' % (tablename, name, def_))

    def _all_constraints(self):
        return self.connection.execute("""
            SELECT c.relname, conname, pg_get_constraintdef(r.oid, false) as condef
                     FROM  pg_constraint r, pg_class c
                     WHERE r.contype = 'f'
                    and r.conrelid=c.oid
            """).fetchall()

if __name__ == '__main__':
    # example usage

    from sqlalchemy import create_engine

    engine = create_engine('postgresql://user:pass@host/dbname', echo=True)

    conn = engine.connect()
    with no_contraints(conn):
        r = conn.execute("delete from table1")
        print "%d rows affected" % r.rowcount
        r = conn.execute("delete from table2")
        print "%d rows affected" % r.rowcount