Foreign Key on Inheriting Table?

From: Alex Satrapa <alex(at)lintelsys(dot)com(dot)au>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Foreign Key on Inheriting Table?
Date: 2004-02-08 22:10:00
Message-ID: 4026B3B8.4050104@lintelsys.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2004-02-08 22:54:35 Re: Foreign Key on Inheriting Table?
Previous Message W. van den Akker 2004-02-08 20:53:12 Re: Extract transaction logging