ActiveRecord from Ruby on Rails actually has a pretty good example of this. They call it polymorphic association.
The idea is this. A normal association (which is Rails' word for "foreign key") consists of a field called
author_id whose integer value is a valid
id in an
authors table somewhere.
Now suppose we have a
books table, but we want to split the authors out. Rather than having a single
authors table, we want to have two:
machines. Either a human or a machine can write a book, so the
books table should foreign key to one of those. With a polymorphic association, we define two fields in the
author_type is the name (as a string) of the table we're linking to. In this example, it's either
author_id is an integer which is a valid
id in whatever table we're referencing.
Now, in Rails' case, all of this is enforced at the Ruby level. Rails just removes the foreign key constraint on polymorphic associations, so as far as the database is concerned, these are just ordinary string and integer fields. As far as I know, there's no SQL database software out there that supports this paradigm directly.
But I could imagine one that does. I could imagine a database where, in addition to
FOREIGN KEY constraints, we have something called a
POLYMORPHIC KEY constraint. This constraint applies to two fields on a table and demands, at the database level, that the
_type string exist as a table (and optionally be one of a set of enumerated values, if we want to restrict which tables we can reference) and that the
_id integer field exist as a valid
id on that table. The constraint understands the key's linkage and can handle
ON DELETE constraints, cascading, nulling, or protecting the source data as needed.
JOINs are optimized for use cases that involve a string comparison followed by an
id check, so we optimize for this case:
FROM books b
JOIN humans h ON b.author_type = 'human' AND b.author_id = h.id