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.