Обсуждение: Table Inheritance and foreign key problem.

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)


NOTE
if the C (id) references the b (id) the insert in  table C
is possible.

I try this to postgresql 8.1.2 and 8.1.4


above is the exact SQL STATMENTS:

============================================

DROP TABLE a;
DROP TABLE b;
DROP TABLE c;

CREATE TABLE  a (id integer primary key);

CREATE TABLE  b (id integer primary key) INHERITS (a);

CREATE TABLE c (id integer primary key references a(id));

insert into b values (1);





SELECT * from b;
 id
----
  1
(1 row)


SELECT * from a;
 id
----
  1
(1 row)


INSERT INTO c VALUES (1);
ERROR:  insert or update on table "c" violates foreign key constraint
"c_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "a".



============================================


Thanks

Kostas Maistrelis.

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)

This is a known, documented shortcoming of our inheritance
implementation.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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