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
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 |