table inheritance and foreign keys

From: Reece Hart <rkh(at)gene(dot)COM>
To: pgsql-general(at)postgresql(dot)org
Subject: table inheritance and foreign keys
Date: 2003-05-15 00:07:57
Message-ID: 1052957276.5829.53.camel@tallac
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'd appreciate some advice regarding the use of foreign keys in
inherited tables. Specifically, here's the schema I'd like:

A S
-------- --------
id serial pkey <---1:1---> id integer references A(id)
name text +-/
|
A1 |
-------- |
(inherits A) <--1:1-+
id serial
C1 text

This doesn't work because the foreign key constraint can only bind to
one table. In other words, I'd like S.id to reference the union of id
keys from A and A1. This is analogous to the union select generated by
'select ... from A'.

Is there a way to achieve the same end by a different design (with
reasonable effort)? The best I can think of is to make A1 a distinct
table joinable on id, and then create a view with insert rule which
separately inserts into A and A1.

I've used table inheritance extensively and find it quite useful.
Extending the OO abstraction to a table's indices would enable even more
concise and expressive designs.

Thanks,
Reece

--
Reece Hart, Ph.D. rkh(at)gene(dot)com, http://www.gene.com/
Genentech, Inc. 650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://www.in-machina.com/~reece/
South San Francisco, CA 94080-4990 reece(at)in-machina(dot)com, GPG: 0x25EC91A0

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2003-05-15 00:48:12 Re: How many levels a B-tree has?
Previous Message Hadley Willan 2003-05-14 22:33:23 Newbie question about logging