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...