Обсуждение: Foreign keys and inheritance

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?

TIA!

kj

On Mon, 2007-11-19 at 14:36 -0500, Kynn Jones wrote:
> 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?
>

I would probably do something like:

CREATE TABLE A (a_id INT PRIMARY KEY, a_attr text);
CREATE TABLE B (b_id INT PRIMARY KEY,
  a_id int references A(a_id) UNIQUE, b_attr text);
CREATE TABLE T (t_id INT PRIMARY KEY,
  a_id int references A(a_id), t_attr text);

I can't tell whether you mean that every A has many T or vice versa, but
minor modification will make it work in the opposite direction.

To look at all A objects, you just look in table A.
You can do "A NATURAL JOIN T" to realize the many-to-one relationship
from A to T.
You can do "A NATURAL JOIN B" to see all B objects (which have a_attr
since they are a special case of A).

This is a normal relational design that is very flexible and doesn't
require the PostgreSQL-specific "INHERITANCE" feature. You don't need to
use natrual joins of course, it was just easier for this example.

Regards,
    Jeff Davis

Kynn Jones wrote:
> 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 seems that inheritance is precisely what you want.

WRT yout table T you should be able to join to B in the same way you
would join to A. But perhaps you should give an example of both B & T
(and maybe A).

brian

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)

On Mon, 2007-11-19 at 17:19 -0300, Alvaro Herrera wrote:
> 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.
>

I don't think it's that bad of a situation. It would be great if
PostgreSQL did support keys across tables, but it's not necessary for a
good design in his case.

The difference between using inheritance and just using multiple tables
(like the alternative that I suggested) is the difference between
vertically partitioning and horizontally partitioning. Both seem like
good choices to me.

Regards,
    Jeff Davis