By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,695 Members | 1,956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,695 IT Pros & Developers. It's quick & easy.

Foreign Key to Inherited table

P: n/a
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);

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.