Inheritance and column references problem

[Scott Goodwin]

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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Stephan Szabo]

> 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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Scott Goodwin]

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. ;)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)