Re: Inheritance and column references problem

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