Thread: Foreign keys referencing parent table fails on insert

Foreign keys referencing parent table fails on insert if the key is in
the table that inherits the parent table.

I am using psql (PostgreSQL) 7.4.2. I have created a trivial case for an
example.

create table trivial1 (id numeric primary key, numval numeric);
create table trivial2 (textval text)inherits (trivial1);
insert into trivial2 values(1,1,'test1');
create table trivial3 (id numeric, subkey numeric,CONSTRAINT trivial3_c1
PRIMARY KEY (id, subkey),FOREIGN KEY (id) REFERENCES trivial1(id));
insert into trivial3 VALUES (1,0);
ERROR:  insert or update on table "trivial3" violates foreign key
constraint "$1"
DETAIL:  Key (id)=(1) is not present in table "trivial1".
select * from trivial1;
 id | numval
----+--------
  1 |      1
(1 row)

While it is true in the strict sense that the key is not in table
"trivial1", the key is in the table "trivial1" in the general sense
since a select on the table does retrieve the key value.

There is an additional problem in that the primary key is not enforced
across the parent (inherited) and child (inheriting) tables. This really
should not work but does.
insert into trivial1 values (1,1);
INSERT 640907 1
A subsequent select give the impression of a violated primary key. I
realize this is not true in the strict sense but appears as a violation
because of the inheritance.
select * from trivial1;
 id | numval
----+--------
  1 |      1
  1 |      1
(2 rows)

Creating trivial2 using
    create table trivial2 (textVal text, primary key (id))inherits
(trivial1);
does not change the results.

Am I missing something?

Regards,
John Griffiths

On Wed, 1 Dec 2004, postgresbugs wrote:

> Foreign keys referencing parent table fails on insert if the key is in
> the table that inherits the parent table.

Unfortunately, primary keys, unique constraints and foreign keys do not
inherit to children in a meaningful fashion. It's one of the big
deficiencies right now.

On Wed, 1 Dec 2004, Stephan Szabo wrote:

  On Wed, 1 Dec 2004, postgresbugs wrote:



    > Foreign keys referencing parent table fails on insert if the key is in
> the table that inherits the parent table.



Unfortunately, primary keys, unique constraints and foreign keys do not
inherit to children in a meaningful fashion. It's one of the big
deficiencies right now.




Too bad. Table inheritance is pretty meaningless without these
abilities. Anyone working on them? In the meantime, I will go back to
views and updating the underlying tables.

On Thu, 2 Dec 2004, postgresbugs wrote:

>
>  On Wed, 1 Dec 2004, Stephan Szabo wrote:
>
>  On Wed, 1 Dec 2004, postgresbugs wrote:
>
>  > Foreign keys referencing parent table fails on insert if the key is in
> > the table that inherits the parent table.
>
>
>  Unfortunately, primary keys, unique constraints and foreign keys do not
> inherit to children in a meaningful fashion. It's one of the big
> deficiencies right now.
>
>
>
>
> Too bad. Table inheritance is pretty meaningless without these
> abilities. Anyone working on them? In the meantime, I will go back to
> views and updating the underlying tables.

There's noone actively working on it at the moment that I know of (and if
there is, hopefully they'll see this and correct me) although I believe
there was some noise in the last dev cycle of people thinking about the
unique (and pkey) side of it.

Stephan Szabo wrote:
<blockquote cite="mid20041202203001.L37573@megazone.bigpanda.com"
 type="cite">
  On Thu, 2 Dec 2004, postgresbugs wrote:



     On Wed, 1 Dec 2004, Stephan Szabo wrote:

 On Wed, 1 Dec 2004, postgresbugs wrote:

 > Foreign keys referencing parent table fails on insert if the key is in


      the table that inherits the parent table.



 Unfortunately, primary keys, unique constraints and foreign keys do not
inherit to children in a meaningful fashion. It's one of the big
deficiencies right now.




Too bad. Table inheritance is pretty meaningless without these
abilities. Anyone working on them? In the meantime, I will go back to
views and updating the underlying tables.



There's noone actively working on it at the moment that I know of (and if
there is, hopefully they'll see this and correct me) although I believe
there was some noise in the last dev cycle of people thinking about the
unique (and pkey) side of it.


Thanks. I would think that if the primary and unique keys worked
properly the foreign key problem would just fall out naturally. I would
volunteer for this except I do all my programing in JAVA these days and
my C and C++ are too rusty to be useful.

I hope someone picks this up. It would go a long way in doing away with
the problems of views and inserts and updates.