Обсуждение: foreign key violation error with partitioned table

hello,

i have a partitioned table t_kayit with  6 partitions and kayit_id is primary key on this table. My other t_vto_sonuclari table use that kayit_id as foreign key. I'm trying to insert  values which contains kayit_id to t_vto_sonuclari and i'm sure those  kayit_ids are   in t_kayit table but  when i'm inserting , i had  error.

ERROR:  insert or update on table "t_vto_sonuclari" violates foreign key constraint "fk_t_kayit_kayit_id"
DETAIL:  Key(kayit_id)=(54168) is not present in table t_kayit


I created a new test table which is as same  as t_kayit but non partitioned . I create new foreign key on that t_vto_sonuclari which refers to the new test table.  In that case i didn't had that error.I could insert. what am i supposed to do for partitioned table?

Hello,

    You are correct in assuming that foreign keys are useless on a
partitioned table's primary key.  The reason for this is that
PostgreSQL uses inheritance to create the functionality of
partitioning.  A partitioned table is really an empty table with
multiple child tables all inheriting from the main partitioned table.
Each child table keeps its own index of the primary key.  Therefore,
when you specify that a column REFERENCES a column in the main
partitioned table, the partitioned table has no way of knowing in
which of the child tables' indexes the key will be stored.  See the
section 5.8.1 called "Caveats" in the chapter about Inheritence, which
explains that the use of foreign keys against inherited tables is
useless:

    http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html

I just recently ran in to this myself while using inheritance.  It was
a frustrating experience... but makes sense when you think about
it.  :)  Good luck to you.

--
Alfred J. Fazio,
alfred.fazio@gmail.com

On Sep 6, 4:49 am, gunceor...@gmail.com ("gunce orman") wrote:
> hello,
>
> i have a partitioned table t_kayit with  6 partitions and kayit_id is
> primary key on this table. My other t_vto_sonuclari table use that kayit_id
> as foreign key. I'm trying to insert  values which contains kayit_id to
> t_vto_sonuclari and i'm sure those  kayit_ids are   in t_kayit table but
> when i'm inserting , i had  error.
>
> ERROR:  insert or update on table "t_vto_sonuclari" violates foreign key
> constraint "fk_t_kayit_kayit_id"
> DETAIL:  Key(kayit_id)=(54168) is not present in table t_kayit
>
> I created a new test table which is as same  as t_kayit but non partitioned
> . I create new foreign key on that t_vto_sonuclari which refers to the new
> test table.  In that case i didn't had that error.I could insert. what am i
> supposed to do for partitioned table?