table inheritance and foreign keys

Поиск
Список
Период
Сортировка
От Reece Hart
Тема table inheritance and foreign keys
Дата
Msg-id 1052957276.5829.53.camel@tallac
обсуждение исходный текст
Ответы Re: table inheritance and foreign keys  (James Gregory <james@anchor.net.au>)
Список 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@gene.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@in-machina.com, GPG: 0x25EC91A0


В списке pgsql-general по дате отправления:

Предыдущее
От: Hadley Willan
Дата:
Сообщение: Newbie question about logging
Следующее
От: "Matthew Nuzum"
Дата:
Сообщение: Re: - what protocol for an Internet postgres