references constraint on inherited tables?

references constraint on inherited tables?

Post by Douglas Bat » Sun, 15 Jul 2001 05:29:38



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.

---------------------------(end of broadcast)---------------------------

 
 
 

references constraint on inherited tables?

Post by Stephan Sza » Sun, 15 Jul 2001 06:29:31


Yes, references constraints reference only the named table (if you look at
the triggers, they use ONLY).  There's a lot of reasons for this,
partially a matter of making sure the triggers inherit properly, partially
making sure all the locking is right, and partially other things that
are wierd across inheritance trees (for example, nothing will prevent
you from updating an article to have the same id as a book, while you'd
expect that the primary key on title would have that effect).

One workaround that was come up with was using an id table and having
the master ids there and the inheritance tree reference those.  Then
anything that wants to reference by id and doesn't care what type it
is references the id table.


Quote:> 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);

---------------------------(end of broadcast)---------------------------


 
 
 

1. Inherited tables vs non-inherited tables

I've been trying an inherited-table schema for my database and seem to
be getting a performance hit. The old table looked like this:

CREATE TABLE center_out (
    subject                     text,
    arm                         char,
    target                      int4,
    rep                         int4,
    success                     int2,       -- end of primary key
    exp_date                    date,
    exp_time                    time,  
    inter_target_radius         int4,
    target_radius               int4);

Since the fields subject, arm, target, and rep appeared in just about
every other table as the primary key, I made it an inherited table for
the new schema:

CREATE TABLE center_out (
        subject    text,
        arm        char,
        target     int2,
        rep        int4,
        success    int2    
        );

CREATE UNIQUE INDEX pkcenter_out ON center_out (subject, arm, target,
rep, success);

CREATE TABLE center_out_behavior (
    exp_date                     date,
    exp_time                     time,
    inter_target_radius          int2,
    target_radius                int2
) INHERITS (center_out);

However, queries such as "SELECT DISTINCT subject FROM center_out"
seem to take 2-3 times longer in the new schema. Does this make sense?
I was hoping that the inherited information would lead to an
easier-to-follow, smaller, and faster database since there wouldn't be
redundant information across tables.

Thanks.
-Tony

 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

2. A magic Query....!!!

3. No reference to inherited columns?

4. Have problem about Perl DBI, Please help

5. Loading Tables with Reference constraints

6. Can′t get Access to Access with the BDE

7. locking of referenced table during constraint construction

8. PERSONAL LIBRARY DATABASE?

9. URGENT: dropping constraints and references from a table

10. self-referencing table constraint

11. locking of referenced table during constraint

12. REFERENCING TABLES, AND USING A ALPHABETIC INTEGRITY CONSTRAINT

13. the replicated table is "referenced by a FOREIGN KEY constraint