Thread: references constraint on inherited tables?
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.
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. On 13 Jul 2001, Douglas Bates wrote: > 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);