There's some mention in the (old!) documentation that constraints such as foreign keys won't include data from
inheritingtables, 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
theobject-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