Foreign key constraint with some column values ​​found in other tables

Question

What is the correct / idiomatic way of expressing a foreign key constraint in PostgreSQL, where some of the FK columns are in another table?

I use an example to make this clear (excluding some obvious PCs and FCs to make it short). We want to model the following associations between books, topics found in a book, reading events (in which a book is read) and topics discussed in a reading event (which should be a subset of book topics):

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

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

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

Then a table to store the various topics that 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 will appear to record information about the “event reads”. Only one book is read in each reading:

 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 hard part. We also have a table for recording topics that were actively discussed during this 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 can I express that the themeDiscussed column should explicitly refer to one of the topics actually found in the book that was read in this case? The column bookName is in the READING_EVENT table, and not in READING_EVENT_DISCUSSION , where we want to declare FK.

Answer 1

You have left all foreign keys in the book name.

That's why I answer with a full extended set of table definitions, this applies to foreign keys, right? Sorry, you gave a stripped-down example.

The problem is that the entries in reading_event_discussion should be about those that exist in this 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 validation function. This is not portable for other database systems.

You can write this function in several languages ​​(plpgsql, pltcl, ...), but SQL functions can be built into the query and can be faster.