<p>The <a href="https://www.scattered-thoughts.net/writing/against-sql/" rel="noreferrer">blog</a> post states that a major drawback of SQL is its lack of sum type support.</p>
<p>Assuming we could redesign SQL today (without necessarily having to worry about compatibility) what are some specific features that could be added in  formal support of sum types?</p>

<types><algebraic-data-types>
How to incorporate sum types into SQL?
CC BY-SA 4.0
2023-08-25T15:08:04.517Z
This question is rather broad. Asking “what would be the best approach” could mean lots of different things, and generally [we are looking for more focused design questions here](https://langdev.meta.stackexchange.com/q/543). Is there a specific aspect that you think would be challenging or difficult?
Alexis King
CC BY-SA 4.0
2023-08-26T16:44:11.620Z
One complication to this question: the Cartesian product in a category of relations gives a [biproduct](https://ncatlab.org/nlab/show/biproduct), an object which is simultaneously a product type and a sum type. So, in a certain sense, SQL already has sums. Perhaps we can be more specific and ask about disjoint unions.
Corbin
CC BY-SA 4.0
2024-01-18T23:38:57.093Z
@Corbin "the Cartesian product in a category of relations gives a biproduct" -

The *disjoint union* of sets is a biproduct in Rel (<https://ncatlab.org/nlab/show/Rel#LimitsAndColimit>).
The cartesian product of sets doesn't satisfy the coproduct universal property when we're in Rel (I can produce a counterexample if needed).

So disjoint unions / sum types are important because without them Rel is not really a model of SQL.
lightandlight
CC BY-SA 4.0
2024-01-19T05:37:36.510Z
@lightandlight: You're entirely correct; where I said "Cartesian product" I should have said "disjoint union," making my comment half-wrong. Thanks for the correction.
Corbin
<p>SQL has sum types in the sense that columns can be optionally populated.</p>
<p>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.</p>
<p>What's missing, though is formality:</p>
<ul>
<li>a construct for grouping multiple columns into one variant of a sum type,
<ul>
<li>to be able to require columns of variant to be non-null when the row has that variant type,</li>
<li>to require one and only one variant's column group is populated</li>
</ul>
</li>
<li>to name the sum type variant / column group</li>
<li>to identify the sum type variant of each row in the table</li>
</ul>
<p>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.</p>
<p>A variant declaration along with variant type declaration column, would ensure mutual exclusion between variants as is appropriate for sum types.</p>
<p>With this approach you can express a sum type in a single table.</p>
<p>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.</p>

CC BY-SA 4.0
2023-08-27T12:14:20.453Z
2023-08-28T19:16:01.630Z
2023-08-28T19:16:01.630Z
Erik Eidt
CC BY-SA 4.0
2023-08-28T17:34:40.943Z
The approach in your last paragraph is the one given in the blog post. I think your first approach is more common, at least in simple "attribute-value" tables. They'll have columns like `value_int`, `value_float`, `value_str`, etc.
Barmar
CC BY-SA 4.0
2023-08-28T19:14:25.057Z
@Barmar, the approach I describe in the last one is meant to use the mutual exclusion feature proposed here; I would not forgo the *nested columns, a variant declaration for a table, a column type for expressing variant type, and query features to properly access the variants* that I'm proposing, when using foreign keys.
Erik Eidt
CC BY-SA 4.0
2023-08-29T10:12:01.840Z
The approach of using one column per summand only works if the sum only has primitive summands. In the general case you could have complex summands. Now you either need a reference to a table or multiple rows per summand. Support for primitive summands would still be progress, though.
Christian Lindig
CC BY-SA 4.0
2023-08-29T11:11:41.313Z
@ChristianLindig, yes, that's what nested columns would provide.
Erik Eidt
<p><a href="https://guides.rubyonrails.org/active_record_basics.html" rel="nofollow noreferrer">ActiveRecord</a> from Ruby on Rails actually has a pretty good example of this. They call it <a href="https://guides.rubyonrails.org/association_basics.html#polymorphic-associations" rel="nofollow noreferrer">polymorphic association</a>.</p>
<p>The idea is this. A <em>normal</em> association (which is Rails' word for &quot;foreign key&quot;) consists of a field called <code>author_id</code> whose integer value is a valid <code>id</code> in an <code>authors</code> table somewhere.</p>
<p>Now suppose we have a <code>books</code> table, but we want to split the authors out. Rather than having a single <code>authors</code> table, we want to have two: <code>humans</code> and <code>machines</code>. Either a human or a machine can write a book, so the <code>books</code> table should foreign key to <em>one</em> of those. With a polymorphic association, we define <em>two</em> fields in the <code>books</code> table.</p>
<ul>
<li><code>author_type</code> is the <em>name</em> (as a string) of the table we're linking to. In this example, it's either <code>&quot;humans&quot;</code> or <code>&quot;machines&quot;</code>.</li>
<li><code>author_id</code> is an integer which is a valid <code>id</code> in whatever table we're referencing.</li>
</ul>
<p>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.</p>
<p>But I could imagine one that does. I could imagine a database where, in addition to <code>FOREIGN KEY</code> constraints, we have something called a <code>POLYMORPHIC KEY</code> constraint. This constraint applies to <em>two</em> fields on a table and demands, at the database level, that the <code>_type</code> 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 <code>_id</code> integer field exist as a valid <code>id</code> on that table. The constraint understands the key's linkage and can handle <code>ON DELETE</code> constraints, cascading, nulling, or protecting the source data as needed. <code>JOIN</code>s are optimized for use cases that involve a string comparison followed by an <code>id</code> check, so we optimize for this case:</p>
<pre><code>SELECT *
  FROM books b
  JOIN humans h ON b.author_type = 'human' AND b.author_id = h.id
</code></pre>

CC BY-SA 4.0
2023-08-26T15:19:56.917Z
2023-08-26T15:19:56.917Z
Silvio Mayolo
CC BY-SA 4.0
2023-09-22T02:23:40.063Z
You don’t need the column with a string for the table name, a foreign key constraint will contain the name of the table.  You need a way to declare a conditional foreign key, which isn’t a problem because null is recognized as no FK restraint needed.  So, how do you get the null, with a computed value of course.  This will work today with SQL SERVER: computed persisted column, FK from books to humans.  What is needed to keep it from being too tedious I would be to eliminate the persisted columns and allow FK based on inline calculations.
jmoreno
<p>There are existing SQL implementations that do allow “sum types”.  Notably, <a href="https://www.sqlite.org/flextypegood.html" rel="nofollow noreferrer">SQLite</a>, with its dynamic typing system.  But even otherwise statically-typed SQLs like Oracle (with its <a href="https://oracle-base.com/articles/misc/anydata" rel="nofollow noreferrer"><code>ANYDATA</code></a> type) or Microsoft (with <a href="https://learn.microsoft.com/en-us/sql/t-sql/data-types/sql-variant-transact-sql?view=sql-server-ver16" rel="nofollow noreferrer"><code>SQL_VARIANT</code></a>) have ways of declaring that a column can contain mixed data types.</p>

CC BY-SA 4.0
2023-09-21T20:57:04.230Z
2023-09-21T20:57:04.230Z
dan04
CC BY-SA 4.0
2023-12-29T18:19:33.603Z
SQLite has something like an ANY type, but since any type can go in any column, it's more like strictly speaking every column is ANY type.
qwr
# How to incorporate sum types into SQL?
Posted by: username_0 on 2023-08-25
The [blog](https://www.scattered-thoughts.net/writing/against-sql/) post states that a major drawback of SQL is its lack of sum type support.

Assuming we could redesign SQL today (without necessarily having to worry about compatibility) what are some specific features that could be added in formal support of sum types?
**Tags:** types algebraic-data-types
> **Comments:**
> **username_1**: This question is rather broad. Asking “what would be the best approach” could mean lots of different things, and generally [we are looking for more focused design questions here](https://langdev.meta.stackexchange.com/q/543). Is there a specific aspect that you think would be challenging or difficult?  
> **username_2**: One complication to this question: the Cartesian product in a category of relations gives a [biproduct](https://ncatlab.org/nlab/show/biproduct), an object which is simultaneously a product type and a sum type. So, in a certain sense, SQL already has sums. Perhaps we can be more specific and ask about disjoint unions.  
> **username_3**: @Corbin "the Cartesian product in a category of relations gives a biproduct" -

The *disjoint union* of sets is a biproduct in Rel (<https://ncatlab.org/nlab/show/Rel#LimitsAndColimit>).
The cartesian product of sets doesn't satisfy the coproduct universal property when we're in Rel (I can produce a counterexample if needed).

So disjoint unions / sum types are important because without them Rel is not really a model of SQL.  
> **username_2**: @lightandlight: You're entirely correct; where I said "Cartesian product" I should have said "disjoint union," making my comment half-wrong. Thanks for the correction.  
---
## 3 answers
### Answer by username_4 on 2023-08-27. Score: 5
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.
> **Comments:**
> **username_5** (2023-08-28): The approach in your last paragraph is the one given in the blog post. I think your first approach is more common, at least in simple "attribute-value" tables. They'll have columns like `value_int`, `value_float`, `value_str`, etc.  
> **username_4** (2023-08-28): @Barmar, the approach I describe in the last one is meant to use the mutual exclusion feature proposed here; I would not forgo the *nested columns, a variant declaration for a table, a column type for expressing variant type, and query features to properly access the variants* that I'm proposing, when using foreign keys.  
> **username_6** (2023-08-29): The approach of using one column per summand only works if the sum only has primitive summands. In the general case you could have complex summands. Now you either need a reference to a table or multiple rows per summand. Support for primitive summands would still be progress, though.  
> **username_4** (2023-08-29): @ChristianLindig, yes, that's what nested columns would provide.  
---
### Answer by username_7 on 2023-08-26. Score: 3
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
> **Comments:**
> **username_8** (2023-09-22): You don’t need the column with a string for the table name, a foreign key constraint will contain the name of the table.  You need a way to declare a conditional foreign key, which isn’t a problem because null is recognized as no FK restraint needed.  So, how do you get the null, with a computed value of course.  This will work today with SQL SERVER: computed persisted column, FK from books to humans.  What is needed to keep it from being too tedious I would be to eliminate the persisted columns and allow FK based on inline calculations.  
---
### Answer by username_9 on 2023-09-21. Score: 3
There are existing SQL implementations that do allow “sum types”. Notably, [SQLite](https://www.sqlite.org/flextypegood.html), with its dynamic typing system. But even otherwise statically-typed SQLs like Oracle (with its [`ANYDATA`](https://oracle-base.com/articles/misc/anydata) type) or Microsoft (with [`SQL_VARIANT`](https://learn.microsoft.com/en-us/sql/t-sql/data-types/sql-variant-transact-sql?view=sql-server-ver16)) have ways of declaring that a column can contain mixed data types.
> **Comments:**
> **username_10** (2023-12-29): SQLite has something like an ANY type, but since any type can go in any column, it's more like strictly speaking every column is ANY type.  
---
