外键约束，某些列值位于其他表中 [英] Foreign key constraint with some column values residing in other tables

问题描述

在其中FK列的一部分位于另一个表中的PostgreSQL中表达外键约束的正确/惯用方式是什么？

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

我将使用一个示例来弄清楚这一点（省略一些明显的PK和FK来缩短这一时间）。我们希望在书籍，书籍中的主题，阅读事件（在其中阅读书籍）与阅读事件中讨论的主题（应该是书籍主题的子集）之间建立以下关联：

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

在SQL方面，我们有一个表用于保存图书：

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

现在，我该如何表达主题讨论了专栏必须明显引用在该事件中读过的书中实际发现的主题之一？ bookName 列显示在 READING_EVENT 表中，而不是在 READING_EVENT_DISCUSSION 我们希望在其中声明FK。

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.

推荐答案

您已经省略了书名上的所有外键。

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.

要解决的问题是， reading_event_discussion 中的记录必须与以下主题相关：存在于该书中：

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.

一个人可以用几种语言（plpgsql，pltcl等）编写此函数，但是SQL函数可以内联到一个查询，可能会更快。

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

这篇关于外键约束，某些列值位于其他表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！