postgresql POSTGRESQL外键引用两个不同表的主键

POSTGRESQL Foreign Key Referencing Primary Keys of two Different Tables

提问by Jason Zhu

I have two tables Books and Audiobooks, both of which have ISBN as their primary keys. I have a table writtenby that has an isbn attribute that has a foreign key constraint to Books and Audiobooks ISBN. The issue that comes up when I insert into writtenby is that postgresql wants the ISBN I insert into writtenby to be in both Books and Audiobooks. It makes sense to me to have a table writtenby that stores authors and the books/audiobooks they have written, however this does not translate to a table in postgresql. The alternative solution I am thinking of implementing was having two new relations audiobook_writtenby and books_writtenby but I am not sure that is a good alternative. Could you give me an idea of how I would implement my original idea of having a single table writtenby referencing two different tables or how I could better design my database. Let me know if you need more information.

我有两个表 Books 和 Audiobooks，它们都以 ISBN 作为主键。我有一个 writeby 表，它有一个 isbn 属性，该属性对 Books 和 Audiobooks ISBN 具有外键约束。当我插入到 writeby 时出现的问题是 postgresql 希望我插入到 writeby 的 ISBN 同时出现在书籍和有声读物中。对我来说，有一个表来存储作者和他们写的书/有声读物是有意义的，但这并不能转换为 postgresql 中的表。我正在考虑实施的替代解决方案是有两个新的关系 audiobook_writtenby 和 books_writtenby 但我不确定这是一个很好的选择。你能给我一个想法，我将如何实现我最初的想法，即通过引用两个不同的表来编写一个表，或者我如何更好地设计我的数据库。如果您需要更多信息，请与我们联系。

回答by Mike Sherrill 'Cat Recall'

There's more than one way to do this in PostgreSQL. Personally, I prefer this way.

在 PostgreSQL 中有不止一种方法可以做到这一点。就个人而言，我更喜欢这种方式。

-- This table should contain all the columns common to both 
-- audio books and printed books.
create table books (
  isbn char(13) primary key,
  title varchar(100) not null,
  book_type char(1) not null default 'p'
    check(book_type in ('a', 'p')),
  -- This unique constraint lets the tables books_printed and books_audio 
  -- target the isbn *and* the type in a foreign key constraint.
  -- This prevents you from having an audio book in this table 
  -- linked to a printed book in another table.
  unique (isbn, book_type)
);

-- Columns unique to printed books.
create table books_printed (
  isbn char(13) primary key references books (isbn),
  -- Allows only one value. This plus the FK constraint below guarantee
  -- that this row will relate to a printed book row, not an audio book
  -- row, in the table books. The table "books_audio" is similar.
  book_type char(1) default 'p'
    check (book_type = 'p'),
  foreign key (isbn, book_type) references books (isbn, book_type),
  other_columns_for_printed_books char(1) default '?'
);

-- Columns unique to audio books.
create table books_audio (
  isbn char(13) primary key references books (isbn),
  book_type char(1) default 'a'
    check (book_type = 'a'),
  foreign key (isbn, book_type) references books (isbn, book_type),
  other_columns_for_audio_books char(1) default '?'
);

-- Authors are common to both audio and printed books, so the isbn here
-- references the table of books.
create table book_authors (
  isbn char(13) not null references books (isbn),
  author_id integer not null references authors (author_id), -- not shown
  primary key (isbn, author_id)
);

回答by kgrittn

You could use table inheritance to kinda get the best of both worlds. Create the audiobook_writtenby and books_writtenby with an INHERITSclause referencing the writtenby table. The foreign keys could be defined at the child level as you describe, but you could still reference data at the higher level. (You could also do this with a view, but it sounds like inheritance might be cleaner in this case.)

您可以使用表继承来两全其美。使用INHERITS引用writeby表的子句创建 audiobook_writtenby 和 books_writtenby 。外键可以按照您的描述在子级别定义，但您仍然可以在更高级别引用数据。（您也可以使用视图执行此操作，但听起来在这种情况下继承可能更清晰。）

See the docs:

查看文档：

http://www.postgresql.org/docs/current/interactive/sql-createtable.html

http://www.postgresql.org/docs/current/interactive/sql-createtable.html

http://www.postgresql.org/docs/current/interactive/tutorial-inheritance.html

http://www.postgresql.org/docs/current/interactive/tutorial-inheritance.html

http://www.postgresql.org/docs/current/interactive/ddl-inherit.html

http://www.postgresql.org/docs/current/interactive/ddl-inherit.html

Note that you will probably want to add a BEFORE INSERT trigger on the writtenby table if you do this.

请注意，如果您这样做，您可能希望在 writeby 表上添加一个 BEFORE INSERT 触发器。

回答by dbenhur

RDBMS's do not support polymorphic foreign key constraints. What you want to do is reasonable, but not something accommodated well by the relational model and one of the real problems of object relational impedance mismatch when making ORM systems. Nice discussion on this on Ward's WIki

RDBMS 不支持多态外键约束。您想要做的是合理的，但不是关系模型很好地适应的事情，并且是制作 ORM 系统时对象关系阻抗不匹配的真正问题之一。在 Ward 的 WIki 上对此进行了很好的讨论

One approach to your problem might be to make a separate table, known_isbns, and set up constraints and/or triggers on Books and AudioBooks so that table contains all the valid isbns of both type specific book tables. Then your FK constraint on writtenby will check against known_isbns.

解决您的问题的一种方法可能是制作一个单独的表，known_isbns，并在 Books 和 AudioBooks 上设置约束和/或触发器，以便该表包含两种特定类型书籍表的所有有效 isbn。然后你对 writeby 的 FK 约束将检查 known_isbns。

回答by Dennis

In this specific example there is absolutely no need to use multiple tables. Just use the table "Book" and add the columns from "AudioBook", if applicable. If you have to differentiate on table level with very specific columns, create views. Have you checked to see if a "Book" and an "Audio Book" with the same content have the same ISBN?

在这个特定示例中，绝对不需要使用多个表。如果适用，只需使用表“Book”并添加“AudioBook”中的列。如果您必须使用非常特定的列在表级别进行区分，请创建视图。您是否检查过内容相同的“书籍”和“有声读物”是否具有相同的 ISBN？