Foreign Key Constraint With Some Column Values Residing In Other Tables

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 (o

Solution 1:

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:

droptable book cascade;
droptable book_theme;
droptable reading_event cascade;
droptable reading_event_discussion;

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

createtable book_theme (
    bookname  text references book(name), -- new
    themename text
);
insertinto book_theme (bookname, themename) values 
  ('game of thrones', 'ambition'), ('game of thrones', 'power');

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

-- this is the solution: extended reference checkcreateor replace function themecheck (i integer, th text) returnsbooleanas $$
    select 
     (th in (select themename from book_theme bt 
       join reading_event re on i=re.i and re.bookRead=bt.bookname))
$$ languagesql;

createtable reading_event_discussion (
    i integerreferences reading_event(i), 
    themeDiscussed text check (themecheck (i, themeDiscussed))
);

-- Test statements:-- just check dataselect*from reading_event;
-- this should be okinsertinto reading_event_discussion values (1,'ambition'),(1,'power');
-- this must be refusedinsertinto 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.