On Sun, 2006-07-23 at 14:59 +0300, Kostas Maistrelis wrote: > I make 3 tables with this senario: > b (id) extends a (id) > and > c (id) references a (id) > > i insert one record to table b (id = 1) > and then i try to insert a record in table C (id = 1) > but it is not possible, i get error > (look at the sql statements above)
I was also caught by the observation you now face. The origin of the issue is that inherited tables do not share a common index on the primary key. In your example, PostgreSQL will also let you have a record with id=1 in table a and b simultaneously, which almost certainly violates your design intentions. PostgreSQL's inheritance is better used to facilitate abstraction and administration of table definitions than it is to provide OO-like subclassing of data. It's likely folly (but not impossible) to use inheritance to represent relationships among abstract representations of instances (er, rows). In the one case where I wanted OO-like behavior badly enough, I did the equivalent of creating a "supertable" c and defined inheriting tables c_a with FK c_a(id) that refers to a(id) and table c_b with FK c_b(id) that refers to table b(id). All c rows with keys that refer to a(id) must be in table c_a, and similarly for b. Fortunately, select from c does a union select of c_a and c_b, and thus provides the abstract view of all instances/rows of c. This would surely be a painful route for more general relationships among complex data. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
