referential integrity with inheritance

From: "Colin Fox" <cfox(at)cfconsulting(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: referential integrity with inheritance
Date: 2002-09-04 03:50:32
Message-ID: pan.2002.09.04.03.50.32.226069@cfconsulting.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Darren Ferguson 2002-09-04 03:54:26 Re: parameterized views?
Previous Message Gregory Seidman 2002-09-04 03:49:30 Re: parameterized views?