Re: Inheritance and foreign keys

On Wed, 14 Jan 2004, Daniel Harris wrote:

> I've been reading on the postgres mailing list about the shortfalls of
> inheritance and wanted to make sure that my problem is linked to this
> popular problem of inheritance not working as you expect.

It looks like it.

> I'd also like to know, (as I could not find much on the list) the best
> recognised solution from users in the know to the following problem:
>
> create table base (id serial primary key, name text);
> create table base_sub () inherits(base);
>
> create table link (base_id integer references base);
>
> Note I couldn't reference base_sub as you would expect to do, for the
> following error:
> ERROR: there is no primary key for referenced table "base_sub"

This is as you guessed because there's no primary key on base_sub because
it doesn't inherit (in fact, there's no check at all in the above to
prevent base_sub from having duplicate values). You could add a primary
key constraint to base_sub. That would mean that you couldn't say insert
two id=1 rows in base_sub, but you'd still be able to insert one in base
and one in base_sub. :(

> insert into base_sub (name) values ('one');
> insert into link (base_id) values (1);
>
> Gives this error:
> ERROR: insert or update on table "link" violates foreign key constraint
> "$1"
> DETAIL: Key (base_id)=(1) is not present in table "base".

In this case, it's erroring because the foreign key only references values
in base itself and not any of the subtables.

> If anyone knows a nice solution to the problem, I've only been working
> with postgres for a couple of weeks and I'm still learning the ropes.
> All I know at the moment is that I'd be a shame to have to hack around
> this problem with an ugly fix; inheritance and foreign key support
> working together properly would be a *very* nice feature to have.

Right now that's about all you can manage. The workaround generally
involves using a second table to store the ids and then referencing that
table.