467,890 Members | 1,890 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,890 developers. It's quick & easy.

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

Nov 22 '05 #1
  • viewed: 4663
Share:
3 Replies

On Mon, 9 Feb 2004, Alex Satrapa wrote:
Does anyone know whether I'm just doing something wrong, or is the old documentation still correct?


This still works the same way it has.

If you look through the archives, you should hopefully be able to scrounge
up some of the workaround information that's been sent in the past.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #2
Stephan Szabo wrote:
This still works the same way it has.

If you look through the archives, you should hopefully be able to scrounge
up some of the workaround information that's been sent in the past.


For reference, if anyone finds this thread through Google or whatever, here is the result of some quick Googling on my part. As an aside, if you want to find answer instead of questions in your Google search, add the phrase "Stephan Szabo" - I get the impression that Stephan has the "use a different table for the unique index for the hierarchy" answer in a .sig by now ;)

Workaround 1: Use a separate table for the field (eg: "id") that is going to be used as the foreign key, and have all tables in the hierarchy reference that table:
* http://archives.postgresql.org/pgsql...6/msg01036.php
* http://archives.postgresql.org/pgsql...1/msg00149.php

Workaround 2: Create unique indexes on each of the child tables in the hierarchy to allow that column on that child table to be used as a foreign key:
* http://archives.postgresql.org/pgsql...2/msg00131.php

From what I've read, the problem arises because the (unique) index doesn't descend an inheritance hierarchy. This causes two problems - the first being that child tables won't have the "unique" index on their inherited columns. The second problem is that the uniqueness check doesn't apply to data inserted into any tables other than the original parent. I guess that's just two different sides of the same coin - the index doesn't span the hierarchy, therefore the uniqueness doesn't span the hierarchy either. Any uniqueness is specific to a child table *iff* a unique index is specified on that child table.

I am not a PostgreSQL programmer, and I doubt I'll get any time to look at the code in the depth required to propose a solution to this problem, so I'll settle for re-documenting Stephan's suggestion. How much would it break existing code to have PostgreSQL issue a warning or notice when someone attempts to CREATE (UNIQUE) INDEX on a column that is inherited? At least the programmer would then be alerted to the problem before it bites them.

For the record, this "foreign key not working on tables defined using inherit" issue affects all versions of PostgreSQL up to at least 7.4.1.

HTH
Alex Satrapa
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #3
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

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeremiah Jacks | last post: by
2 posts views Thread by geoff | last post: by
10 posts views Thread by Bodza Bodza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.