Thread: referencial conlumn contraints and inheritance

Folks,

  I am not sure where to post this.  This may be more of a wishlist item
than a usage question.  I am new to postgresql 7.1.  Table inheritance
in postgres seems wonderfull.  Is there the capability now or any 
plans to extend support for inherited tables in column constraints. One 
example is a travel db where several tables are defined as:

create table areas (
area_id char(10) primary key,
name varchar(40),
.
description varchar(120) );

create table countries (
.
. ) inherits (areas);

create table states (
. 
country_id char(10) references countries ...,
.
. ) inherits (areas);

create table cities (
. 
country_id char(10) references countries ...,
.
.) inherits (areas);

Then a destination table set up with a field referencing areas. 
Currently postgres appears not to support reference contraints checking
through inherited tables.  Nor does it appear to support subselects in check
constraints.  It would be nice to allow reference checking through the
inherited tables so destination.area_id would be valid for any country,
state, or city area_id entered.

create table destination (
. 
area_id char(10) references areas ...,
.
.);

Thanks,

Stuart

Stuart,

>   I am not sure where to post this.  This may be more of a wishlist
> item
> than a usage question.  I am new to postgresql 7.1.  Table
> inheritance
> in postgres seems wonderfull.  Is there the capability now or any
> plans to extend support for inherited tables in column constraints.
> One
> example is a travel db where several tables are defined as:

There have been, as I understand it, several improvements made to
inheritance and constraints in 7.2 (released soon, really!).

However, you're potentially in for a world of painful workarounds here.
REFERENCES constraints are part of a Relational DBMS system; Inheritance
is an OODB concept.  The two do not, IMHO, mix easily.  Further, current
implementations of table inheritance are largely proprietary, meaning
that your application becomes impossible to part across platforms.

If I were designing your database, I would forget about inheritance, and
build it completely relationally, either with an tree-structured Areas
table keyed to area subtypes, or with heirarchical tables of country -->
region --> province --> city etc.  Which structure you use depends
largely on the rest of the application.

However, keep in mind that a lot of people (the Postgres core team
included) do not agree with me about inheritance and its limitations.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

On Sun, 14 Oct 2001, Stuart wrote:

> Then a destination table set up with a field referencing areas. 
> Currently postgres appears not to support reference contraints checking
> through inherited tables. �Nor does it appear to support subselects in check
> constraints. �It would be nice to allow reference checking through the
> inherited tables so destination.area_id would be valid for any country,
> state, or city area_id entered.

This isn't currently supported, and probably isn't going to be supported
for a version or two at least.  There are some problems in the current
inheritance scheme (for example, IIRC your primary key is also not
inherited, and even if you define one on the child it won't stop you from
having duplicates between the tables) and this is one of them.  The work
to do this isn't so huge, but there are some issues about inheriting the
triggers that would need to be sat down and thought about.

Stephan Szabo and Josh Berkus,
Thanks for your input.  The question at hand seemed to provide an appealing 
argument for providing inheritance support for referencial constraints, but 
if its support is as rare as it seems to be then that could produce problems 
if I want the ability to be able to manage the data with tools on other 
platforms.  I guess I will have to give potential alternatives more 
consideration.


Thanks again,

Stuart