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)