Re: Foreign Key on Inheriting Table?

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(at)lintelsys(dot)com(dot)au>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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