mardi 3 février 2015

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?





Aucun commentaire:

Enregistrer un commentaire