Re: Inherited tables and column references

On Thu, 19 Feb 2004, Scott Goodwin wrote:

> In the following example:
>
> create table parent (
>     id         integer unique not null,
>     name   varchar(24)
> );
>
> create table child (
>     first_name   varchar(256),
>     last_name   varchar(256)
> ) inherits (parent);
>
> create table relations (
>     id        integer references child (id)
> );
>
> Creating the relations table fails with:
>
>     ERROR: there is no unique constraint maching given keys for
> referenced table "child"
>
> If I change the last table to:
>
> create table relations (
>     id        integer references parent (id)
> );
>
> it works.
>
> This essentially means that references must point to the real table
> where the referenced column is, and cannot point to that same column in
> a table that inherits the real table.
>
> is this by design, is it a bug, is it just not implemented yet, or am I
> doing this wrong?

There are two separate things here that are gotchas
The first is that unique constraints don't inherit, and foreign keys must
refer to a unique constraint.  You can make a unique constraint on child
and then you should be allowed to reference to it.  However, that
constraint will not prevent a value already in parent from also being used
in child.
The second is that references don't inherit either, so if you reference
parent for example, rows in child will not be considered for purposes of
satifying the constraint.

These are both deficiencies in inheritance and the constraints in
question.  They're likely to get fixed eventually, but AFAIK noone's
really stepped up to do alot of the work.