Re: Table Inheritance and foreign key problem.

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