pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreferencial conlumn contraints and inheritance
4+ messages / 3 participants
[nested] [flat]
* referencial conlumn contraints and inheritance
@ 2001-10-14 11:28 Stuart <sfbarbee@yahoo.com>
2001-10-14 18:54 ` Re: referencial conlumn contraints and inheritance Josh Berkus <josh@agliodbs.com>
2001-10-14 23:08 ` Re: referencial conlumn contraints and inheritance Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-17 13:04 ` Re: referencial conlumn contraints and inheritance Stuart <sfbarbee@yahoo.com>
0 siblings, 3 replies; 4+ messages in thread
From: Stuart @ 2001-10-14 11:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: referencial conlumn contraints and inheritance
2001-10-14 11:28 referencial conlumn contraints and inheritance Stuart <sfbarbee@yahoo.com>
@ 2001-10-14 18:54 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2001-10-14 18:54 UTC (permalink / raw)
To: sfbarbee@yahoo.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: referencial conlumn contraints and inheritance
2001-10-14 11:28 referencial conlumn contraints and inheritance Stuart <sfbarbee@yahoo.com>
@ 2001-10-14 23:08 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2001-10-14 23:08 UTC (permalink / raw)
To: Stuart <sfbarbee@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: referencial conlumn contraints and inheritance
2001-10-14 11:28 referencial conlumn contraints and inheritance Stuart <sfbarbee@yahoo.com>
@ 2001-10-17 13:04 ` Stuart <sfbarbee@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Stuart @ 2001-10-17 13:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-10-17 13:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-14 11:28 referencial conlumn contraints and inheritance Stuart <sfbarbee@yahoo.com>
2001-10-14 18:54 ` Josh Berkus <josh@agliodbs.com>
2001-10-14 23:08 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-17 13:04 ` Stuart <sfbarbee@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox