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" <al**@lintelsys.com.au>
To: "PostgreSQL General" <pg***********@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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
