Re: Foreign Key on Inheriting Table? - Mailing list pgsql-general

Alex,

[N.B. I just read the second message you sent on this question, but I have a
solution that goes in a different direction.]

I ran into the same problem after designing a system based on inheritance,
and asked a similar question a couple of weeks ago -- a couple of folks here
gave very helpful replies to my query.

I ended up ditching the table inheritance mechanism, because I really need
foreign keys to work on all records in base and inheriting tables. So
instead I linked tables with foreign keys on the ids, then defined views
with rules on insert/update/delete to make it all work like inheritance. I
actually like the result better, but it's more work to set it up. Like so:

create table objects (
    id serial primary key,
    name varchar
) without oids;

create table documents (
    id integer primary key references objects (id) on delete cascade,
    body text
) without oids;

create or replace view documents_objects as
    select objects.*, body from objects, documents
    where objects.id = documents.id;

create table articles (
    id integer primary key references documents (id) on delete cascade,
    title varchar
) without oids;

create or replace view articles_objects (
    select documents_objects.*, title from documents_objects, articles
    where documents_objects.id = articles.id;

<etc>
<add rules, functions, and triggers; stir until thickened.>

FWIW,
Shawn Harrison

----- Original Message -----
From: "Alex Satrapa" <alex@lintelsys.com.au>
To: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: Sunday, February 08, 2004 4:10 PM
Subject: [GENERAL] Foreign Key on Inheriting Table?


> There's some mention in the (old!) documentation that constraints such as
foreign keys won't include data from inheriting tables, eg:
>
>   CREATE TABLE foo (
>      id SERIAL PRIMARY KEY
>   );
>
>   CREATE TABLE bar (
>      attribute integer NOT NULL
>   ) INHERITS (foo);
>
>   CREATE TABLE bar_widgets (
>      bar integer CONSTRAINT bar_exists REFERENCES foo (id)
>   );
>
>
> Now if you populate bar, you can't put anything in bar_widgets, because
the foreign key constraint is not satisfied.
>
> Similarly, if you want to have self-referencing items (eg: two points link
together):
>
>   CREATE TABLE anomalies (
>      id integer PRIMARY KEY,
>      x integer NOT NULL,
>      y integer NOT NULL
>   );
>
>   CREATE TABLE wormholes (
>      other_end integer CONSTRAINT has_end REFERENCES wormholes (id)
>   ) INHERITS (anomalies);
>
>
> This won't work because the wormholes tables doesn't actually have the id
column (the anomalies table has it).
>
> This won't work either:
>
>   CREATE TABLE wormhole_tubes (
>      left_end integer CONSTRAINT left_exists REFERENCES wormholes (id),
>      right_end integer CONSTRAINT right_exists REFERENCES wormholes (id)
>   );
>
>
> While I could adjust my code to treat wormholes separately to all other
anomalies, I was hoping to take advantage of the object-relational features
of PostgreSQL to make my work a little easier.
>
> Does anyone know whether I'm just doing something wrong, or is the old
documentation still correct?
>
> Thanks
> Alex Satrapa
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend