Florian,
Are you certain:
"You can only create an FK if the fields you are referencing in the
foreign table form a PK there. And creating a PK implicitly creates an
index, which you can't drop without dropping the PK :-("
????
I'm not sure I am convinced the necessity of a foreign key, "needing" to
reference a primary keyed entry from a different table.
Florian G. Pflug wrote:
Wes wrote:
You could create the fk-constraints _first_, then disable them, load
the data, reindex, and reenable them afterwards.
pg_dump/pg_restore can enable and disable fk-constraints before
restoring
the data, I believe. It does so by tweaking the system catalogs.
Are referring to '--disable-triggers' on pg_dump? Will this work for
foreign key constraints? The doc talks about triggers, but doesn't say
anything about FK constraints (are these implemented as triggers?) I
don't
use pg_restore, just psql.
Yes, I was referring to "--disable-triggers". I always assumes that it
disables FK-Constraints as well as triggers, but now that you ask I
realize that I might have never actually tried that ;-)
But FK-Constraints _are_ implemented as triggers internally, so I
guess it should work.
The only problem I can see is that creating the fk-constraints might
create
some indices too. But maybe you can manually drop those indices
afterwards - I
don't know if the fk really _depends_ on the index, or if it creates
it only
for convenience.
I don't see any indexes being added to the table beyond what I add, and
those added as a primary key constraint. Currently, pg_dump outputs
the FK
constraints after the indexes are built, as the last steps. If I try
to add
the FK constraints after loading the database definitions, but
without any
indexes, I'm not sure what would happen.
Hm.. it i tried it out, and came to the conclusion that my approach
doesn't work :-(
You can only create an FK if the fields you are referencing in the
foreign table form a PK there. And creating a PK implicitly creates an
index, which you can't drop without dropping the PK :-(
So unless you find a way to force postgres to ignore the index when
inserting data, my suggestion won't work :-(
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
