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.

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