Referential integrity after disable/enable trigger all

I wonder how to check referential integrity on a table after disabling and reenabling foreign key constraints.

For example :

postgres=# CREATE TABLE main(id serial, id_delta integer, PRIMARY KEY (id));
CREATE TABLE
postgres=# CREATE TABLE delta (id serial, PRIMARY KEY (id));
CREATE TABLE
postgres=# ALTER TABLE main ADD CONSTRAINT fk_main_delta FOREIGN KEY (id_delta) REFERENCES delta (id);
ALTER TABLE

Let's try to insert a value that do not respect fk_main_delta:

postgres=# INSERT INTO main (id_delta) VALUES (1);
ERROR:  insert or update on table "main" violates foreign key constraint "fk_main_delta"
DÉTAIL : Key (id_delta)=(1) is not present in table "delta".

=> it logically fails, so I disable FK constraints to force insert:

postgres=# ALTER TABLE main DISABLE TRIGGER ALL;
postgres=# INSERT INTO main (id_delta) VALUES (1);
INSERT 0 1
postgres=# ALTER TABLE main ENABLE TRIGGER ALL;

But now, referential integrity is no longer respected.

postgres=# SELECT * FROM main;
 id | id_delta 
----+----------
  2 |        1
(1 ligne)

So is there a way to check integrity afterwards?

As it seems that there is no way to check referential integrity after using DISABLE TRIGGER ALL / ENABLE TRIGGER ALL, I finally used DROP/CREATE CONSTRAINT instead.

Inspired by this post on Magnus Hagander's blog I used such queries to generate alter commands for removing and creating FK:

-- Generate ALTER TABLE ... ADD CONSTRAINT
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" ADD CONSTRAINT "'||conname||'" '||
pg_get_constraintdef(pg_constraint.oid)||';'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
WHERE pg_constraint.contype = 'f'
ORDER BY nspname DESC, relname DESC, conname DESC

-- Generate ALTER TABLE ... DROP CONSTRAINT
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" DROP CONSTRAINT "'||conname||'";'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
WHERE pg_constraint.contype = 'f'
ORDER BY nspname, relname, conname

Here is my process:

• Start Transaction

• Drop Constraints

• Do some stuff... (insert, update, whatever...)

• Create Constraints

• if it fails then rollback, else Commit Transaction

StackExchange Database Administrators Q#90902, answer score: 3