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
>