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?
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? |