Foreign Key to Inherited table

From: Greg Hulands <ghulands(at)bigpond(dot)net(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: Foreign Key to Inherited table
Date: 2003-11-04 06:24:49
Message-ID: 975ECFE0-0E8F-11D8-A921-0003937807BE@bigpond.net.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ang Chin Han 2003-11-04 06:50:35 Re: Please help
Previous Message Joe Conway 2003-11-04 05:59:05 Re: How to use dblink within pl/pgsql function: