4

enter image description here

Lets say I want to setup a roles table that has a polymorphic relationship to resource.

I understand that that I could directly setup a foreign key - by adding for example a roles.forum_id column.

But why is (AFAIK) creating a compound foreign key where one column holds the key and another the table to reference not possible?

6

A foreign key column references a single table. If you want to reference a different table as a foreign key, you have to have a different or second foreign key column. You can't have a single column that is a union type of different foreign keys (the first column of your suggested compound key). There is also no column type for table itself (the second part of your suggested compound key).

You can fake this by using a strings for keys and strings for tables (yuk!), but it will impact all of your tables and their queries rather dramatically.


All of this is part of what's known as the Object-Relational Mismatch .

Polymorphism by class is simply not a feature of the standard relational model, though some specific databases feature inheritance as an extension.

Support for polymorphism and/or inheritance is primarily featured in relational model as attributes: (1) attributes can be optional, which is they can store null instead of a value, and (2) new attributes can be added at will to support new subclasses. So, probably the cleanest map to a class with subclasses is to model every attribute in the same, single table, with attributes for subclasses as optional attributes, while attributes for the base class are required (if so).

This works pretty well for some stuff, but is not perfect in at least the following:

  1. there is no special identification of what true subclass a particular row is from, and,
  2. there is no way to ensure that the required attributes of a particular subclass are all present when the row is intended as an instance of that particular subclass (because we had to mark the subclass's attributes as optional).

For more information on modeling inheritance in relational db's, see:

0

It is perfectly possible to model polymorphic data in a relational database. E.g. a series of nullable foreign keys to each resource type table, and a constraint that exactly one is non-null.

  • I know of at least one RBDMS that does not allow nullable foreign keys. – RubberDuck Sep 25 '16 at 22:57
  • @RubberDuck Is the question about modeling foreign key relationships or about availability of declarative referential integrity? Every RDBMS allows nullable foreign keys, if it allows nullable columns. Not every RDBMS offers declarative referential integrity for foreign keys. I worked for years on RDBMS with no declarative RI capability at all, e.g. early Oracle. We still had foreign keys. Caleth's answer is correct, as long as the database allows nullable columns. – joshp Sep 25 '16 at 23:23
  • @joshp as far as I'm concerned, if referential integrity isn't enforced, you can't really call it a foreign key. Let's agree to disagree. – RubberDuck Sep 25 '16 at 23:32
  • @RubberDuck It's not really a question of agree or disagree. It's a misunderstanding about what a foreign key is. It is a data modeling concept that exists regardless of implementation or enforcement, whether we like it or not. It is also a language construct in SQL that has various specific implementations. The question is which does OP mean? I think he means he can't get declarative RI for the structure he wants. But he is not so clear about it. He CAN model the relationship the way he wants. He CAN'T get declarative RI for it from any RDBMS I know of. Modeling it Caleth's way he gets RI. – joshp Sep 25 '16 at 23:59
  • 1
    This answer is correct, but could be harmful without further explanations about the consequences of such a design, and without mentioning better alternatives. – Doc Brown Sep 26 '16 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.