POSTGRESQL Foreign Key Referencing Primary Keys of two Different Tables

Question

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.

question from:https://stackoverflow.com/questions/10068033/postgresql-foreign-key-referencing-primary-keys-of-two-different-tables

Answer 1

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

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