On Mon, Aug 30, 2010 at 5:30 AM, Carlos Henrique Reimer <carlos.reimer@xxxxxxxxxxxxx> wrote: > 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 > > Hi, I guess you could consider the following strategy: Halt the server or lock the table or something so no program is allowed to delete any rows on the affected tables. Run a PL/SQL script that will remove rows from "posicoes_controles" whose foreign key is not present in table "posics." Then re-issue the foreign key constraint. Then unlock the table or whatever it is you have to do get programs to be able to use the tables again. I hope this helps somewhat. -- George H george.dma@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
