referential integrity with inheritance

Поиск
Список
Период
Сортировка
От Colin Fox
Тема referential integrity with inheritance
Дата
Msg-id pan.2002.09.04.03.50.32.226069@cfconsulting.ca
обсуждение исходный текст
Ответы Re: referential integrity with inheritance  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список pgsql-general
Hi, All.
I'm setting up a simple accounting system, and I want to have accounts,
companies and people.

Accounts may be held by either people or companies. So what I tried was
creating an abstract type called Entities, and deriving people and
companies from Entities.

Then I tried saying:
create table acct_holders
   (
   id serial,
   entity_id integer not null references entities(id),
   account_id integer not null references accounts(id),
   primary key(id)
   );

(I also noticed that although the documentation says that to get all
results from derived tables, I'd have to suffix the parent tablename with
'*', as in "select * from entities*;". But right now, when I select from
just 'entities', I get the derived tables (postgres 7.2.2). Bug?)

Anyway - when I try to insert into the acct_holders table, I get:

ERROR:  <unnamed> referential integrity violation - key referenced from
acct_holders not found in entities

I have a corresponding record in the Companies table, which is derived
from Entities. Is there any way I can make the reference checker happy?
Since I want both People and Companies to have accounts, I need the
reference checker to make sure the reference is in "entities or any sub
table".

I thought I could do it with:
..entity_id integer not null references entities*(id)
as the syntax is for a select, but I get a syntax error.

Any suggestions?

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Call closed: Almost happy ending (from "Data files became huge with no apparent reason" thread)
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: referential integrity with inheritance