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?
