Re: referential integrity with inheritance

On Wed, 4 Sep 2002, Colin Fox wrote:

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

The default behavior was changed (I think for 7.2) to cascade to children
by default. You can use ONLY <table> now in the from iirc to get only
the single table.

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

Unfortunately there's currently no direct way to do it. The best
workaround described so far puts the ids in a separate table with all
of the tables referencing to that (including the tables in the hierarchy).
In addition, primary keys and such do not inherit successfully either,
so doing the above also gives you the ability to get a unique constraint
on the id (since you could insert directly even into the serial column).