Thread: Table Inheritance and Foreign Keys
Good day
I’ve got a scenario where I’m building a table structure that allows for building a tree. In order to properly separate the different column requirements I’ve looked at using table inheritance.
Example structure:
CREATE TABLE base_table
(
id bigint primary key,
parent_id bigint,
some_common_columns ….
)
CREATE TABLE folder
(
id bigint primary key,
some_folder_specific_columns …
) INHERITS(base_table)
CREATE TABLE node_type_x
(
id bigint primary key,
some_node_x_specific_columns….
) INHERITS (base_table)
Now obviously when inserting folders and node type x records they would go to their respective tables, but with the table inheritance I can query base_table to get them all.
The rules state that folders can only be inserted under folders, but node type x can go under folders or other node type x’s.
Now if I want to add a foreign key to the base table from node type x I get an error due to Postgres not supported this scenario. The documentation talks about this, but also state as follows
“These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your application”
My question is if anybody knows more as to if and when this actually will be allowed/solved.
Regards
Riaan
"Riaan Stander" <rstander@exa.co.za> writes:
> I've got a scenario where I'm building a table structure that allows for
> building a tree. In order to properly separate the different column
> requirements I've looked at using table inheritance.
> [ but can't point a foreign key at an inheritance tree ]
> My question is if anybody knows more as to if and when this actually will be
> allowed/solved.
Don't hold your breath :-(. It's been like that for decades.
We do, as of v11, support foreign keys referencing partitioned tables,
but that relies on the ability to declare a unique constraint across
a partitioned table, which in turn relies on there being a known
partitioning key (which has to be part of the unique column set).
Generic inheritance doesn't have that much semantic knowledge about
how the data is divvied up, so it's not really practical to support
these things in a generic inheritance tree.
Probably we should revise that bit of documentation to point people
at the partitioning features; I doubt anything's ever going to be
done about this for generic inheritance.
regards, tom lane