Thread: Inherited tables and column references

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?


/s.

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.

Sounds right to me -- inherited table "child" doesn't really have a key of
it's own in your schema -- it's parent has the key... the child table is
basically the parent table plus some extra columns...

To do what it looks like you want to do -- you would need separate primary
keys on both parent and child but it doesn't seem exactly "normalized" to
me... this makes more sense...

To see what inheritance is really all about do this:

CREATE TABLE person (

    id integer not null primary key,
    first_name varchar(40) not null,
    last_name varchar(50) not null

);

CREATE TABLE child (

    child boolean default TRUE

) INHERITS (person);

INSERT INTO PERSON (id, first_name, last_name) VALUES (1, 'George',
'Smith');
INSERT INTO PERSON (id, first_name, last_name) VALUES (2, 'Mary', 'Smith');
INSERT INTO child  (id, first_name, last_name) VALUES (3, 'Baby', 'Smith');
SET SQL_INHERITANCE TO ON;

Then run these 4 queries one at a time...

SELECT * FROM person;
SELECT * FROM ONLY person;
SELECT * FROM child;
SELECT * FROM ONLY child;

--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762

"Scott Goodwin" <scott@scottg.net> wrote in message
news:A960295A-633D-11D8-9E3E-000A95A0910A@scottg.net...
> 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?
>
>
> /s.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>