Am Donnerstag, 1. April 2004 16:24 schrieb Tom Lane: > Janning Vygen <vygen@gmx.de> writes: > > can i check my database for violating FKs if i populated the database > > with FK constraints disabled? > > Drop the constraints and then add them back afterwards. You might want > to think about dropping and rebuilding indexes as well. many thanks. Now i got it. But now i have another question: Is there a way to drop all foreign keys and indices and reinstall them after COPY finished? Maybe it can be done with a magic update statement or sql function. And maybe somebody has already written it or can tell me that this completey nonsens? I dont want to alter my schema by hand and write hundreds of ALTER TABLE statements just to have a faster COPY statement. kind regards janning ------- just for my own clarification i tried both methods for the first problem above. Maybe it clarifies some other brains, too. It works with ALTER TABLE .. DROP CONSTRAINT and ALTER TABLE ADD CONSTRAINT like this: ------- CREATE TABLE foo (foo text); CREATE TABLE bar (bar text); COPY foo from stdin DELIMITERS '|' NULL AS ''; a b \. COPY bar from stdin DELIMITERS '|' NULL AS ''; c d \. ALTER TABLE bar ADD CONSTRAINT fk_foo FOREIGN KEY (bar) REFERENCES foo (foo); ------- results in ERROR: insert or update on table "bar" violates foreign key constraint "fk_foo" DETAIL: Key (bar)=(c) is not present in table "foo". But manipulating the pg_catalog tables like with UPDATE statements like pg_dump does: ------- \connect - postgres CREATE TABLE foo (foo text PRIMARY KEY); CREATE TABLE bar (bar text REFERENCES foo(foo)); UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = 'bar'::pg_catalog.regclass; COPY foo from stdin DELIMITERS '|' NULL AS ''; a b \. COPY bar from stdin DELIMITERS '|' NULL AS ''; c d \. UPDATE pg_catalog.pg_class SET reltriggers=(SELECT pg_catalog.count(*) FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid) WHERE oid = 'bar'::pg_catalog.regclass; ------- Runs without error because FK are enabled but of course not checked when they are enabled again. Ok just wrote this mail for my own clarification, maybe its not of any use for the rest of the world... ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
