Foreign key references now fails with inherited columns

From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: pgsql-hackers(at)hub(dot)org
Subject: Foreign key references now fails with inherited columns
Date: 2000-10-26 21:42:06
Message-ID: 200010262142.e9QLg6M14962@linda.lfix.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(dot)Elphick(at)lfix(dot)co(dot)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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2000-10-26 23:15:14 Summary: what to do about INET/CIDR
Previous Message Tom Lane 2000-10-26 21:36:07 pgsql/src/backend/nodes (copyfuncs.c outfuncs.c print.c)