[GENERAL] Foreign Key on Inheriting Table?

[Alex Satrapa]

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