How to incorporate sum types into SQL?

SQL has sum types in the sense that columns can be optionally populated.

A sum type is then represented by having a table with all the columns necessary for all the fields of all the variants of the sum type.

What's missing, though is formality:

• a construct for grouping multiple columns into one variant of a sum type,
  • to be able to require columns of variant to be non-null when the row has that variant type,
  • to require one and only one variant's column group is populated
• to name the sum type variant / column group
• to identify the sum type variant of each row in the table

So, what I'd do is add named nested columns, a variant declaration for a table, a column type for expressing variant type, and query features to properly access the variants.

A variant declaration along with variant type declaration column, would ensure mutual exclusion between variants as is appropriate for sum types.

With this approach you can express a sum type in a single table.

Or, if you prefer, use foreign keys to other tables, where each foreign key to a different table for each variant is a variant in the main sum type table — but with mutual exclusion of variants feature and the other features from the list that I would add.

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: humans and 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 books table.

• author_type is the name (as a string) of the table we're linking to. In this example, it's either "humans" or "machines".
• 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:

SELECT *
  FROM books b
  JOIN humans h ON b.author_type = 'human' AND b.author_id = h.id

There are existing SQL implementations that do allow “sum types”. Notably, SQLite, with its dynamic typing system. But even otherwise statically-typed SQLs like Oracle (with its ANYDATA type) or Microsoft (with SQL_VARIANT) have ways of declaring that a column can contain mixed data types.