Foreign Key to Inherited table

[Greg Hulands]

I have a table called Person that has a primary key personID. Another
table called ProUser that inherits from Person. In another table called
ProPriceSchedule I have a foreign key constraint to the ProUser table,
like so: CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES
ProUser(personID).

The problem I am having is this: ERROR: UNIQUE constraint matching
given keys for referenced table "prouser" not found

Here are the tables for reference:

CREATE SEQUENCE pro_price_schedule_id;

CREATE TABLE ProPriceSchedule
(
scheduleID int8 PRIMARY KEY DEFAULT NEXTVAL('pro_price_schedule_id'),
personID int8 NOT NULL,
title varchar (32) NOT NULL CHECK (title <> ''),
CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES ProUser(personID)
);

CREATE SEQUENCE user_id;

CREATE TABLE Person
(
personID int8 PRIMARY KEY DEFAULT NEXTVAL('user_id'),
firstName varchar (32) NOT NULL CHECK (firstName <> ''),
lastName varchar (32) NOT NULL CHECK (lastName <> ''),
address1 varchar (64) NOT NULL CHECK (address1 <> ''),
address2 varchar (64) ,
suburb varchar (32) NOT NULL,
stateID int2 NOT NULL,
postcode varchar (8) NOT NULL,
phone varchar (16) ,
mobile varchar (16) ,
email varchar (128) NOT NULL UNIQUE,
password varchar (16) NOT NULL,
CONSTRAINT state FOREIGN KEY (stateID) REFERENCES State(stateID)
);

CREATE TABLE ProUser
(
businessName varchar (64) NOT NULL CHECK (businessName <> ''),
abn varchar (16) NOT NULL CHECK (abn <> ''),
homepageHTML varchar (4096) ,
creditLimit decimal (10, 2) ,
enforceLimit bool NOT NULL,
fax varchar (16)
) INHERITS (Person);

[Stephan Szabo]

On Tue, 4 Nov 2003, Greg Hulands wrote:

I have a table called Person that has a primary key personID. Another
table called ProUser that inherits from Person. In another table called
ProPriceSchedule I have a foreign key constraint to the ProUser table,
like so: CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES
ProUser(personID).

The problem I am having is this: ERROR: UNIQUE constraint matching
given keys for referenced table "prouser" not found

That's because there isn't one. Primary keys, unique constraints and
foreign key constraints don't meaningfully inherit currently. So, for now
ProUser has no unique constraint on the personID field unless you define
one in ProUser (and said constraint does not prevent a personID in Person
from being duplicated in ProUser).

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html