Ограничение внешнего ключа с некоторыми значениями столбца, находящимися в других таблицах

Question

Как правильно / идиоматически выразить ограничение внешнего ключа в PostgreSQL, где часть столбцов FK находится в другой таблице?

Я буду использовать пример, чтобы прояснить это (опуская некоторые очевидные PK и FK, чтобы сделать это коротким). Мы хотим смоделировать следующие ассоциации между книгами, темами, найденными в книге, событиями чтения (в которых книга читается) и темами, обсуждаемыми в событии чтения (которое должно быть подмножеством тем книги):

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

В терминах SQL у нас есть таблица для хранения книг:

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

Тогда таблица, чтобы держать различные темы, которые мы находим в каждой книге:

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

Затем таблица для записи информации о "чтении событий". В каждом событии чтения читается только одна книга:

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');

И тут начинается сложная часть. У нас также есть таблица для записи тем, которые активно обсуждались во время этого чтения:

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

Теперь, как я могу выразить, что themeDiscussed колонка должна явно ссылаться на одну из тем, фактически найденных в книге, которая была прочитана в этом событии? колонка bookName присутствует в READING_EVENT стол, а не в READING_EVENT_DISCUSSION где мы хотим, чтобы ФК был объявлен.

Answer 1

Вы пропустили все внешние ключи в названии книги.

Вот почему я отвечаю с полным расширенным набором определений таблиц, это касается внешних ключей, верно? Уверен, что вы дали урезанный пример.

Проблема была в том, что записи в reading_event_discussion должно быть о темах, которые существуют в этой книге:

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');

Поэтому решение состоит в том, чтобы написать пользовательскую функцию проверки. Это не переносимо на другие системы баз данных.

Можно написать эту функцию на нескольких языках (plpgsql, pltcl, ...), но функции SQL могут быть встроены в запрос и могут быть быстрее.