Re: Inheritance and foreign keys

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.

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"

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".

As I understand it the problem is not being able to create the link
table referencing base_sub as the inherited id field doesn't preserve
it's true primary key status.

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.

Thanks in advance!

-- Dan H.

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.