3

What's the proper / idiomatic way to express a foreign key constraint in PostgreSQL where part of the FK columns resides in another table?

I'll use an example to make this clear (omitting some obvious PKs and FKs to make this short). We wish to model the following associations between books, themes found in a book, reading events (in which a book is read) and themes discussed in a reading event (which should be a subset of a book's themes):

  book ←———————————— reading_event 
   ↑                     ↑
 theme ←———————————— themeDiscussed

In terms of SQL, we have a table used to hold books:

CREATE TABLE BOOK (name VARCHAR);
INSERT INTO BOOK(name) VALUES('game of thrones');

Then a table to hold the various themes we find in each book:

CREATE TABLE BOOK_THEME (bookName VARCHAR, themeName VARCHAR);
INSERT INTO BOOK_THEME(bookName, themeName) VALUES ('game of thrones', 'ambition'), ('game of thrones', 'power');

Then a table to record information about "reading events". Only one book is read in each reading event:

CREATE TABLE READING_EVENT(i SERIAL, venue VARCHAR, bookRead VARCHAR);
ALTER TABLE READING_EVENT ADD PRIMARY KEY (i);
INSERT INTO READING_EVENT(venue, bookRead) VALUES('Municipal Library', 'game of thrones');

And here, comes the tricky part. We also have a table to record the themes that were actively discussed during that reading event:

CREATE TABLE READING_EVENT_DISCUSSION(i INTEGER, themeDiscussed VARCHAR);
ALTER TABLE READING_EVENT_DISCUSSION ADD CONSTRAINT constr1 FOREIGN KEY (i) REFERENCES READING_EVENT(i);

Now, how do I express that the themeDiscussed column has to obviously reference one of the themes actually found in the book that was read in that event? Column bookName is present in the READING_EVENT table, not in the READING_EVENT_DISCUSSION where we want the FK to be declared.

Marcus Junius Brutus
  • 26,087
  • 41
  • 189
  • 331
  • 1
    As it happens, [another question on dba.SE](http://dba.stackexchange.com/questions/58970/enforcing-constraints-two-tables-away) addressed the *exact* same problem today. Consider the question (already including part of the answer to your problem) and my answer. – Erwin Brandstetter Feb 15 '14 at 02:12
  • @ErwinBrandstetter it is a similar but NOT the same situation. I added a schematic which shows the basic correspondence and indeed it seems that it's the same pattern. However there is one crucial difference: in the http://dba.stackexchange.com/q/58970/34332 question the `pin_inst` table is truly redundant (as you point out in your response there). The pin instances of a part instance are always ALL the pins of that part. In my case the themes discussed in a reading event are a *SUBSET* of the themes found in a book. So my understanding is that it's only possible to do it via triggers. – Marcus Junius Brutus Feb 15 '14 at 12:14
  • I find that question rather confusing. The pattern is indeed the same but the details are not clear in that question (for me at least). See this one and the answer: **[Many to Many and Weak Entities](http://dba.stackexchange.com/questions/34040/many-to-many-and-weak-entities/34050#34050)** – ypercubeᵀᴹ Feb 15 '14 at 12:48
  • @ypercube I don't think this present question and the one ErwinBrandstetter referred to are related to the link you provide. – Marcus Junius Brutus Feb 15 '14 at 12:55
  • 1
    Transform `Artist -> Book`, `Album -> ReadingEvent`, `Track -> BookTheme` and `AlbumTrack -> ReadingEventDiscussion`. The only difference is that you don't need the `TrackNo` in `AlbumTrack` and the primary key would be (the mentioned there) `(trackID, albumID) -> (ThemeName, i)` (no Theme allowed twice in an Reading Event.) – ypercubeᵀᴹ Feb 15 '14 at 13:02
  • 1
    @ypercube I stand corrected. The crux of the matter is that in the link you provide, in the Album table, the FK to the Artist table is also part of the Album's primary key whereas both in my case the book read is not part of the reading event's PK and in Erwin's link the part id is not part of the pin's PK. But I agree that using the organization you propose the problem is indeed solved. – Marcus Junius Brutus Feb 15 '14 at 13:10
  • Yes, exactly. You either change the primary key or add unique constraints (that's implementation detail, related to how you want your primary keys in the various tables created, whether they are char or integer or serial and can vary across DBMS.) – ypercubeᵀᴹ Feb 15 '14 at 13:16

1 Answers1

2

You have omitted all the foreign keys on the book name.

That is why I answer with a complete enhanced set of table definitions, this is about foreign keys, right? Shure you gave a stripped down example.

The problem to solve was that records in reading_event_discussion must be about themes that exist in that book:

drop table book cascade;
drop table book_theme;
drop table reading_event cascade;
drop table reading_event_discussion;

create table book (
    name text primary key -- new, a must because it is FK in reading_event
);
insert into book (name) values ('game of thrones'),('Database design');

create table book_theme (
    bookname  text references book(name), -- new
    themename text
);
insert into book_theme (bookname, themename) values 
  ('game of thrones', 'ambition'), ('game of thrones', 'power');

create table reading_event (
  i        SERIAL primary key, 
  venue    text, 
  bookread text references book(name) -- FK is new
);
insert into reading_event (venue, bookRead) VALUES
  ('Municipal Library', 'game of thrones');  

-- this is the solution: extended reference check
create or replace function themecheck (i integer, th text) returns boolean as $$
    select 
     (th in (select themename from book_theme bt 
       join reading_event re on i=re.i and re.bookRead=bt.bookname))
$$ language sql;

create table reading_event_discussion (
    i integer references reading_event(i), 
    themeDiscussed text check (themecheck (i, themeDiscussed))
);

-- Test statements:
-- just check data
select * from reading_event;
-- this should be ok
insert into reading_event_discussion values (1,'ambition'),(1,'power');
-- this must be refused
insert into reading_event_discussion values (1,'databases');

So the solution is to write a custom check function. This is not portable to other database systems.

One can write this function in several languages (plpgsql, pltcl, ... ), but SQL functions can be inlined into a query and might be faster.

Str.
  • 1,389
  • 9
  • 14