Внешний ключ POSTGRESQL, ссылающийся на первичные ключи двух разных таблиц

У меня есть две таблицы Книги и аудиокниги, оба из которых имеют ISBN в качестве их основных ключей. У меня есть таблица, написанная с атрибутом isbn, который имеет ограничение внешнего ключа для книг и аудиокниг ISBN. Проблема, возникающая при вставке в письменном виде, заключается в том, что postgresql хочет, чтобы ISBN я вставлял в нее, чтобы быть в книгах и аудиокнигах. Для меня имеет смысл написать таблицу, в которой хранятся авторы и книги/аудиокниги, которые они написали, однако это не переводится в таблицу в postgresql. Альтернативное решение, которое я собираюсь реализовать, состояло в двух новых отношениях audiobook_writtenby и books_writtenby, но я не уверен, что это хорошая альтернатива. Не могли бы вы дать мне представление о том, как я буду реализовывать свое первоначальное представление о том, чтобы иметь одну таблицу, написанную, ссылаясь на две разные таблицы или на то, как я мог бы лучше создавать свою базу данных. Дайте мне знать, если вам нужна дополнительная информация.

Ответ 1

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

Ответ 2

RDBMS не поддерживают ограничения полиморфного внешнего ключа. То, что вы хотите сделать, является разумным, но не хорошо адаптируется реляционной моделью и одной из реальных проблем несоответствия реляционного импеданса объекта при создании систем ORM. Хорошая дискуссия об этом на Ward WIki

Один из подходов к вашей проблеме может заключаться в создании отдельной таблицы, known_isbns и настройке ограничений и/или триггеров в книгах и аудиокнигах, чтобы таблица содержала все допустимые isbns обеих таблиц книг определенного типа. Тогда ваше ограничение FK на запись будет проверяться на known_isbns.

Ответ 3

Вы можете использовать наследование таблицы, чтобы получить лучшее из обоих миров. Создайте audiobook_writtenby и books_writtenby с предложением INHERITS, ссылающимся на написанную таблицу. Внешние ключи могут быть определены на уровне ребенка по мере описания, но вы все равно можете ссылаться на данные на более высоком уровне. (Вы также можете сделать это с видом, но похоже, что наследование может быть более чистым в этом случае.)

Смотрите документы:

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/ddl-inherit.html

Обратите внимание, что вы, вероятно, захотите добавить триггер BEFORE INSERT в записанную таблицу, если вы это сделаете.

Ответ 4

В этом конкретном примере абсолютно нет необходимости использовать несколько таблиц. Просто используйте таблицу "Книга" и добавьте столбцы из "Аудиокниги", если это применимо. Если вам нужно различать на уровне таблицы с очень конкретными столбцами, создавайте представления. Вы проверили, есть ли в "Книге" и "Аудиокниге" с одним и тем же содержимым один и тот же ISBN?