Search Postgresql Archives

Restore referencial integrity

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,
 
We had by mistake dropped the referencial integrety between two huge tables and now I'm facing the following messages when trying to recreate the foreign key again:
 
alter table posicoes_controles add
  CONSTRAINT protocolo FOREIGN KEY (protocolo)
      REFERENCES posicoes (protocolo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE;
 
ERROR:  insert or update on table "posicoes_controles" violates foreign key constraint "protocolo"
DETAIL:  Key (protocolo)=(338525035) is not present in table "posicoes".
********** Erro **********
ERROR: insert or update on table "posicoes_controles" violates foreign key constraint "protocolo"
SQL state: 23503
Detalhe: Key (protocolo)=(338525035) is not present in table "posicoes".
As the error message tells, the table "posicoes_controles" has values in column "protocolo" that are not present in column "protocolo" of table "posicoes". This happened because some programs removed rows from table "posicoes" while the referencial integrity was dropped.
 
Now I need to remove all rows from table "posicoes_controles" that has not corresponding row in table "posicoes".
 
As these are huge tables, almost 100GB each, and the server hardware restricted (4GB RAM) I would like a suggestion of which command or commands should be used from the performance perspective. 
 
Column "protocolo" is "posicoes" table primary key but is not in any index colum of table "posicoes_controles".
 
Thank you very much for any help!

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@xxxxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux