Thread: BUG #6003: Cannot have a constraint foreign key on master class with inheritance

The following bug has been logged online:

Bug reference:      6003
Logged by:          David carlos Manuelda
Email address:      stormbyte@gmail.com
PostgreSQL version: 9.0.3
Operating system:   Gentoo Linux
Description:        Cannot have a constraint foreign key on master class
with inheritance
Details:

I've reading on the internet and TODO, that there is a similar issue, but I
don't think it is the same.

Besides inheriting constraints of KEYS, it MUST have the hability to FOREIGN
KEY from another external (from inheritance view) table to master
inheritance table.
Without this, inheritance is MUCH limited and hacky.

Proposed:
CREATE TABLE foo (
   id INTEGER NOT NULL,
   ...,
   PRIMARY KEY (id)
);

CREATE TABLE bar (
   otherdata VARCHAR(30) NOT NULL,
   ...,
) INHERITS (foo);

CREATE TABLE problematic_table (
   id SERIAL NOT NULL,
   ....,
   other_id INTEGER NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY other_id REFERENCES foo(id)
);

-- INSERTING DATA
INSERT INTO bar(id,otherdata) VALUES (5,'TEST'); -- OK!
INSERT INTO problematic_table(id,other_id) VALUES (1,5); -- WILL FAIL SINCE
IT WILL NOT FIND VALUE 5 FROM foo, BUT foo HAS ALREADY THAT VALUE STORED

I mean, without this, inheritance is pretty useless, and, in my opinion, it
is one of the greatest features of Postgre!

Is it a duplicate of what is in TODO and I understood it bad, or is this a
new issue?

On Tue, May 03, 2011 at 10:12:53PM +0000, David carlos Manuelda wrote:
> Is it a duplicate of what is in TODO and I understood it bad, or is this a
> new issue?

well, possibly neither. It's limitation well known, and described in
docs:
http://www.postgresql.org/docs/9.0/interactive/ddl-inherit.html
5.8.1 Caveats

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/