Re: Foreign keys and inheritance

Kynn Jones escribió:
> I have two classes of objects, A and B, where B is just a special case
> of A.  (I.e., to describe a B-type object I need to specify the same
> fields as for an A-type object, plus a whole bunch additional fields
> specific to B alone.)  Furthermore, there's a third class T that is in
> a many-to-one relation with A (and hence also B) objects.
>
> The question is, what's the "best practice" for implementing this
> situation in PostgreSQL.  My first idea was to define B as inheriting
> from A, which is OK, except that I have not figured out how to
> implement the reference from T.  Is inheritance indeed the right tool
> for this problem, or should I use a different approach?

It would be the right tool if the FKs worked :-(  Sadly, they don't.

alvherre=# create table foo (a int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
cCREATE TABLE
alvherre=# create table bar (a int not null references foo);
CREATE TABLE
alvherre=# create table baz () inherits (foo);
CREATE TABLE
alvherre=# insert into baz values  (1);
INSERT 0 1
alvherre=# select * from foo;
 a
---
 1
(1 row)

alvherre=# insert into bar values (1);
ERROR:  insert or update on table "bar" violates foreign key constraint "bar_a_fkey"
DETAIL:  Key (a)=(1) is not present in table "foo".


This is a Postgres shortcoming, but I don't think there's anybody
working on fixing it, so don't hold your breath.

Uniqueness also fails in inheritance: for example

alvherre=# insert into foo values (1);
INSERT 0 1
alvherre=# select * from foo;
 a
---
 1
 1
(2 rows)

(Note that column is the PK)

--
Alvaro Herrera                          Developer, http://www.PostgreSQL.org/
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)