POSTGRESQL Foreign Key Referencing Primary Keys of two Different Tables

Asked 2012-04-09T01:32:53.513

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.

postgresql foreign-keys

Edit Report

2 Answers

You could use table inheritance to kinda get the best of both worlds. Create the audiobook_writtenby and books_writtenby with an INHERITS clause 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.)

See the docs:

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

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

answered 2012-04-09T03:04:35.803

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

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.

answered 2012-04-09T01:48:33.503

POSTGRESQL Foreign Key Referencing Primary Keys of two Different Tables

2 Answers

Your Answer