On Thu, Dec 4, 2008 at 8:40 AM, Rüdiger Sörensen
<soerense@mpch-mainz.mpg.de> wrote:
> insert or update in table "t3" violates foreign key constraint "t3_t1_fkey"
> DETAIL: key(t1)=(1) is not present in table "t1"
> select * from t2;
> id | num
> ----+-----
> 1 | 1
> can anyone explain this behaviour?
Yes, PostgreSQL table inheritance is really just Horizontal Table
partitioning with a nifty feature that makes the upper node table
behave more like hierarchical UNION ALL views when SELECTed rather
than actual tables. So in your case, the record you added to t2
doesn't really exits in t1 so referential integrity throws an error in
t3.
> My database setup relies on inherited
> tables that share a primary key.
In this case, I would recommend you use a vertically partitioned table
design that simulates what you are trying to achieve. I recently
developed a presentation on this subject if you are interested:
http://wiki.postgresql.org/images/9/91/Pguswest2008hnd.pdf
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug