references constraint on inherited tables? - Mailing list pgsql-general

From Douglas Bates
Subject references constraint on inherited tables?
Date
Msg-id 6rg0c060s0.fsf@franz.stat.wisc.edu
Whole thread Raw
Responses Re: references constraint on inherited tables?  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
List pgsql-general
I am using PostgreSQL 7.1.1 on a Debian GNU/Linux 3.0 (testing)
system.

In a table definition I used a REFERENCES constraint to a table that
is inherited, thinking that this would ensure that a value for that
column occurred in the table that I named or any of its daughter
tables.  I was wrong.  Apparently the constraint checks only the
table and not any daughter tables.

If I expressed this in terms of SELECT the distinction would be like
that between 'table' and 'table*' (pre-7.1) or between 'ONLY table'
and 'table' (7.1 and later).

Some details on the application may help.  This is a bibliographic
database storing information on books, journal articles, proceedings
articles, book reviews, etc.  We refer to any of these as "titles".
Keywords or phrases are stored in the phrase table.  Phrases and
titles are related through the keywords table.

Some of the table definitions are:

-- All publications must be listed in the title table.

CREATE TABLE title (     -- a virtual table - do not insert into this
  idT      SERIAL       PRIMARY KEY,
  type     CHAR(1)      CHECK(upper(type) IN ('B', 'E', 'J', 'P', 'R')),
  title    TEXT         NOT NULL
);

-- Information on a journal article is in the article table

CREATE TABLE article (
  idE      INTEGER      REFERENCES jour_vol ON UPDATE CASCADE,
  bpg      VARCHAR(5)   ,
  epg      VARCHAR(5)   ,
  PRIMARY KEY (idT)
) INHERITS (title);

-- Book_cont describes the contents of a book

CREATE TABLE book_cont (
   idE      INTEGER     NOT NULL REFERENCES book,
   PRIMARY KEY (idT)
) INHERITS (title);

...

-- Keywords or phrases must be listed in the phrase table

CREATE SEQUENCE idpseq;  -- stores the default idp value

CREATE TABLE phrase (
  idP     INT2          PRIMARY KEY  DEFAULT nextval('idpseq'),
  phrase  VARCHAR(80)   NOT NULL UNIQUE
);

-- A keyword or phrase is related to a title through the keywords table

CREATE TABLE keywords (
  idK     SERIAL     PRIMARY KEY,
  idT     INTEGER    NOT NULL REFERENCES title ON UPDATE CASCADE,
  idP     INT2       NOT NULL REFERENCES phrase ON UPDATE CASCADE,
  kseq    INT2       NOT NULL default 0  CHECK(kseq >= 0)
);

As one of the comments indicates, the title table is a virtual (or
abstract) table.  It exists only to characterize the common
characteristics of all titles.

Are there any suggestions on how I can achieve what I want to do?  We
are at the design stage right now and I can change the design if
needed.

pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Postgresql & Python 2
Next
From: Stephan Szabo
Date:
Subject: Re: references constraint on inherited tables?