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.





---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Wed, 14 Jan 2004, Daniel Harris wrote:

It looks like it.

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.

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

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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings