The following sequence, which works in 7.0.2, now fails in current
sources:

CREATE TABLE person
(
   ptype        SMALLINT,
   id           CHAR(10)        PRIMARY KEY,
   name         TEXT            NOT NULL,
   address      INTEGER         REFERENCES address (id)
                                        ON UPDATE CASCADE
                                        ON DELETE NO ACTION,
   salutation   TEXT            DEFAULT 'Dear Sir',
   envelope     TEXT,
   email        TEXT,
   www          TEXT,
   CONSTRAINT person_ptype CHECK (ptype >= 0 AND ptype <= 8),
   FOREIGN KEY (id, address) REFERENCES address(person, id)
	ON UPDATE CASCADE
	ON DELETE RESTRICT
)
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'person_pkey' for 
table 'person'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
CREATE TABLE individual
(
   gender       CHAR(1)           CHECK (gender = 'M' OR
                                         gender = 'F' OR
                                         gender IS NULL),
   born         DATE              CHECK ((born >= '1 Jan 1880' AND
                                          born <= CURRENT_DATE) OR
                                         born IS NULL),
   surname      TEXT,
   forenames    TEXT,
   title        TEXT,
   old_surname  TEXT,
   mobile       TEXT,
   ni_no        TEXT,
   CONSTRAINT is_named CHECK (NOT (surname IS NULL AND forenames IS NULL)),
   CONSTRAINT individual_ptype CHECK (ptype = 1 OR (ptype >= 5 AND ptype <= 
7)),
   FOREIGN KEY (id, address) REFERENCES address(person, id)
	ON UPDATE CASCADE
	ON DELETE RESTRICT
)
   INHERITS (person)
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  columns referenced in foreign key constraint not found.

I think this means that the FOREIGN KEY installer thinks that table
"individual" does not have columns "id" and "address", which are
inherited from "person".  However, if this is correct, the wording of the
error message is poorly chosen, since it seems to refer to the columns
mentioned after the REFERENCES keyword.


-- 
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
                 ========================================
     "Whosoever therefore shall be ashamed of me and of my 
      words in this adulterous and sinful generation; of him
      also shall the Son of man be ashamed, when he cometh 
      in the glory of his Father with the holy angels."     
                                 Mark 8:38 



Thu, 26 Oct 2000 22:42:06 +0100
"Oliver Elphick" <olly@lfix.co.uk>
Foreign key references now fails with inherited columns
On Thu, 26 Oct 2000, Oliver Elphick wrote:

> The following sequence, which works in 7.0.2, now fails in current
> sources:

> [example snipped]
> 
> I think this means that the FOREIGN KEY installer thinks that table
> "individual" does not have columns "id" and "address", which are
> inherited from "person".  However, if this is correct, the wording of the
> error message is poorly chosen, since it seems to refer to the columns
> mentioned after the REFERENCES keyword.

Yeah, that is what it means, and yes, that was probably a bad choice of
wording (will fix that too).  Hmm, without looking (no access to code from
work unless I get another copy), that probably means that the list of
columns in the function doesn't get those columns (at least by that
point).



Thu, 26 Oct 2000 18:24:41 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Foreign key references now fails with inherited columns