It no longer seems to be possible to refer to a table, which is an
ancestor of any other, in a referential integrity constraint.

In this example, "person" is the ancestor of several other tables:


bray=# create table junk (id char(10) constraint junk_id_person references 
person*(id));
ERROR:  parser: parse error at or near "*"
bray=#  create table junk (id char(10) constraint junk_id_person references 
only person(id));
ERROR:  parser: parse error at or near "only"
bray=# create table junk (id char(10) constraint junk_id_person references 
person(id));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
bray=# insert into junk values ('aa');
ERROR:  SELECT FOR UPDATE is not supported for inherit queries

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "And there were in the same country shepherds abiding 
      in the field, keeping watch over their flock by night.
      And, lo, the angel of the Lord came upon them, and the
      glory of the Lord shone around them; and they were 
      sore afraid. And the angel said unto them, " Fear not;
      for behold I bring you good tidings of great joy which
      shall be to all people. For unto you is born this day 
      in the city of David a Saviour, which is Christ the 
      Lord."        Luke 2:8-11 



Fri, 22 Dec 2000 17:20:19 +0000
"Oliver Elphick" <olly@lfix.co.uk>
RI problem with inherited table
"Oliver Elphick" <olly@lfix.co.uk> writes:
> It no longer seems to be possible to refer to a table, which is an
> ancestor of any other, in a referential integrity constraint.

> bray=# create table junk (id char(10) constraint junk_id_person references 
> person(id));
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> bray=# insert into junk values ('aa');
> ERROR:  SELECT FOR UPDATE is not supported for inherit queries

Hm.  The short-term answer seems to be to modify the queries generated
by the RI triggers to say "ONLY foo".  I am not sure whether we
understand the semantics involved in allowing a REFERENCES target to be
taken as an inheritance tree rather than just one table, but certainly
the current implementation won't handle that correctly.

			regards, tom lane

Fri, 22 Dec 2000 12:52:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RI problem with inherited table 