Inherited tables and column references

[Scott Goodwin]

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

[Greg Patnude]

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
[phone]

"Scott Goodwin" <sc***@scottg.net> wrote in message
news:A9**********************************@scottg.n et...

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

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match