Thread: Storage of Foreign Keys

Hi,

can anybody tell me how postgres internally stores foreign keys...
E.g. I have a table "tableA" with column "mycolA text" and the constraint
FOREIGN KEY(mycolA) REFERENCES tableB(mycolB)

Is everything I insert into mycolA and mycolB stored twice, or
is there something like a pointer/reference from mycolA to the things
stored in mycolB?

Thanks alot for any information, Christian

On Monday 21 September 2009, Christian Koetschan
<c.koetschan@biozentrum.uni-wuerzburg.de> wrote:
> Is everything I insert into mycolA and mycolB stored twice, or
> is there something like a pointer/reference from mycolA to the things
> stored in mycolB?
>

It's stored twice and for performance you need to index it in both tables.
If you put ON UPDATE CASCADE on it, an update to the master will hit both
tables though.

--
"No animals were harmed in the recording of this episode. We tried but that
damn monkey was just too fast."

On Mon, Sep 21, 2009 at 11:09 AM, Alan Hodgson <ahodgson@simkin.ca> wrote:
> On Monday 21 September 2009, Christian Koetschan
> <c.koetschan@biozentrum.uni-wuerzburg.de> wrote:
>> Is everything I insert into mycolA and mycolB stored twice, or
>> is there something like a pointer/reference from mycolA to the things
>> stored in mycolB?
>>
>
> It's stored twice and for performance you need to index it in both tables.
> If you put ON UPDATE CASCADE on it, an update to the master will hit both
> tables though.

Slight correction.  You might need to index it on both ends for
performance.  There are some instances where the referencing table
only ever has a few rows in it, and in those cases, you likely don't
need an index.

Scott Marlowe wrote:
> On Mon, Sep 21, 2009 at 11:09 AM, Alan Hodgson <ahodgson@simkin.ca> wrote:
>> On Monday 21 September 2009, Christian Koetschan
>> <c.koetschan@biozentrum.uni-wuerzburg.de> wrote:
>>> Is everything I insert into mycolA and mycolB stored twice, or
>>> is there something like a pointer/reference from mycolA to the things
>>> stored in mycolB?
>>>
>> It's stored twice and for performance you need to index it in both tables.
>> If you put ON UPDATE CASCADE on it, an update to the master will hit both
>> tables though.
>
> Slight correction.  You might need to index it on both ends for
> performance.  There are some instances where the referencing table
> only ever has a few rows in it, and in those cases, you likely don't
> need an index.

And it is likely that the referenced table has an index since foreign keys
reference a primary key or unique-constrained (combination of) column(s).

--
Lew