Foreign Key to Inherited table

Поиск
Список
Период
Сортировка
От Greg Hulands
Тема Foreign Key to Inherited table
Дата
Msg-id 975ECFE0-0E8F-11D8-A921-0003937807BE@bigpond.net.au
обсуждение исходный текст
Ответы Re: Foreign Key to Inherited table  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список pgsql-general
<fontfamily><param>Verdana</param><x-tad-bigger>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);</x-tad-bigger></fontfamily>
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);

В списке pgsql-general по дате отправления:

Предыдущее
От: Greg Stark
Дата:
Сообщение: Re: Constraint Problem
Следующее
От: "Najib Abi Fadel"
Дата:
Сообщение: Using SUBSELECT in CHECK expressions