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!