Thread: Inheritance and column references problem

The following SQL:

create table toinherit (
     id  integer primary key
);

create table leftside (
     leftname   varchar(64) not null unique
) inherits (toinherit);

create table rightside (
     rightname   varchar(64) not null unique
) inherits (toinherit);

create table linkthem (
     left_id     integer references toinherit (id),
     right_id    integer references toinherit (id)
);

insert into leftside (id, leftname) values (1, 'leftname1');
insert into rightside (id, rightname) values (2, 'rightname2');
insert into linkthem (left_id, right_id) values (1, 2);


...gives me this error:

CREATE TABLE
CREATE TABLE
INSERT 55919 1
INSERT 55920 1
psql:without_inherit.sql:24: ERROR:  insert or update on table
"linkthem" violates foreign key constraint "$1"
DETAIL:  Key (left_id)=(1) is not present in table "toinherit".


If I do the same thing but without using inheritance:

create table toinherit (
     id  integer primary key
);

create table leftside (
     id         integer references toinherit (id),
     leftname   varchar(64) not null unique
);

create table rightside (
     id         integer references toinherit (id),
     rightname   varchar(64) not null unique
);

create table linkthem (
     left_id     integer references toinherit (id),
     right_id    integer references toinherit (id)
);

insert into toinherit (id) values (1);
insert into toinherit (id) values (2);
insert into leftside (id, leftname) values (1, 'leftname1');
insert into rightside (id, rightname) values (2, 'rightname2');
insert into linkthem (left_id, right_id) values (1, 2);

...it works:

CREATE TABLE
CREATE TABLE
INSERT 55887 1
INSERT 55888 1
INSERT 55889 1
INSERT 55890 1
INSERT 55891 1


Is this a bug, or a feature? It seems I can't make a column reference
work directly with the table that gets inherited by the others. Neither
can I make a column reference work with a table that *inherits* the
toinherit table. If I can't get this to work, I'll have to revert back
to not using inheritance at all.

thanks,

/s.

> Is this a bug, or a feature? It seems I can't make a column reference
> work directly with the table that gets inherited by the others. Neither
> can I make a column reference work with a table that *inherits* the
> toinherit table. If I can't get this to work, I'll have to revert back
> to not using inheritance at all.

All of primary key, unique and foreign key constraints don't currently
meaningfully inherit. For the foreign key case the reference goes only
to the directly named table so rows in the subtables are not considered
for purposes of validating the constraint.  In addition, in a structure
where you have a primary key inherited, the child tables do not get the
constraint for uniqueness although that can be added by explicitly putting
the constraint on, however that won't prevent duplicates between the child
and parent nor between children. There are some marginally complicated
workarounds using a separate table that have been discussed on the lists
before and should be available in the archives.

I think I should build a macro for the above. ;)

I knew about child tables not getting the constraint for uniqueness --
I created a rule to do nothing on inserts to the parent table; all
inserts to the child tables had a before insert trigger that did a
select on the parent table to ensure the id I was about to insert was
truly unique. The inability for primary and foreign key constraints to
be inherited breaks what I wanted to do with it.

Still, in the first example I gave, the linkthem table had columns that
referenced the id from the parent table directly, not from the child
table that was inheriting the parent. What you're saying is that if I
insert directly into the parent table, the primary key will work, but
if I insert into the child table, the id will be 'visible' in the
parent, but won't be able to be referenced from a column in any other
table. Something for me to try -- I might still be able to inherit, but
then force all inserts of the primary key into the parent table
directly, with the rest of the fields being inserted into the child.

thanks for the info,

/s.

On Feb 24, 2004, at 7:38 PM, Stephan Szabo wrote:

>> Is this a bug, or a feature? It seems I can't make a column reference
>> work directly with the table that gets inherited by the others.
>> Neither
>> can I make a column reference work with a table that *inherits* the
>> toinherit table. If I can't get this to work, I'll have to revert back
>> to not using inheritance at all.
>
> All of primary key, unique and foreign key constraints don't currently
> meaningfully inherit. For the foreign key case the reference goes only
> to the directly named table so rows in the subtables are not considered
> for purposes of validating the constraint.  In addition, in a structure
> where you have a primary key inherited, the child tables do not get the
> constraint for uniqueness although that can be added by explicitly
> putting
> the constraint on, however that won't prevent duplicates between the
> child
> and parent nor between children. There are some marginally complicated
> workarounds using a separate table that have been discussed on the
> lists
> before and should be available in the archives.
>
> I think I should build a macro for the above. ;)
>

Will the current behavior change in the upcoming releases ?  Currently I'm
investigating if we can replace a proprietary database with postgresql.  For
our object model inheriting the primary key and foreign key constraints is
essential.

Also, Can you please point to some resources which describe ways to circumvent
the problem.

Thank you very much in advance.

Sagar.

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Tuesday, February 24, 2004 4:38 PM
To: Scott Goodwin
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Inheritance and column references problem


> Is this a bug, or a feature? It seems I can't make a column reference
> work directly with the table that gets inherited by the others. Neither
> can I make a column reference work with a table that *inherits* the
> toinherit table. If I can't get this to work, I'll have to revert back
> to not using inheritance at all.

All of primary key, unique and foreign key constraints don't currently
meaningfully inherit. For the foreign key case the reference goes only
to the directly named table so rows in the subtables are not considered
for purposes of validating the constraint.  In addition, in a structure
where you have a primary key inherited, the child tables do not get the
constraint for uniqueness although that can be added by explicitly putting
the constraint on, however that won't prevent duplicates between the child
and parent nor between children. There are some marginally complicated
workarounds using a separate table that have been discussed on the lists
before and should be available in the archives.

I think I should build a macro for the above. ;)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly