Re: Adding foreign key constraints without integrity check?

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