How to emulate tagged union in a database?

Question

What is the best way to emulate Tagged union in databases? I'm talking about something like this:

Click to copy

create table t1 {
  vehicle_id INTEGER NOT NULL REFERENCES car(id) OR motor(id) -- not valid
  ...
}

where vehicle_id would be id in car table OR motor table, and it would know which.

(assume that motor and car tables have nothing in common0

Answer 1

I think the least-boilerplate solution is to use constraint and check.

For example, consider this ADT in Haskell:

Click to copy

data Shape = Circle {radius::Float} | Rectangle {width::Float, height::Float}

The equivalent in MySQL/MariaDB would be (tested on 10.5.11-MariaDB):

Click to copy

CREATE TABLE shape (        
  type ENUM('circle', 'rectangle') NOT NULL,
  radius FLOAT,             
  width FLOAT,              
  height FLOAT,             
  CONSTRAINT constraint_circle CHECK 
    (type <> 'circle' OR radius IS NOT NULL),
  CONSTRAINT constraint_rectangle CHECK 
    (type <> 'rectangle' OR (width IS NOT NULL AND height IS NOT NULL))
);                                      
                                        
INSERT INTO shape(type, radius, width, height)
  VALUES ('circle', 1, NULL, NULL); -- ok
                                                                                                                    
INSERT INTO shape(type, radius, width, height)
  VALUES ('circle', NULL, 1, NULL); -- error, constraint_circle violated

Note that the above uses type <> x OR y instead of type = x AND y. This is because the latter essentially means that all rows must have type of x, which defeats the purpose of tagged union.

Also, note that the solution above only check for required columns, but does not check for extraneous columns.

For example, you can insert a rectangle which has defined radius.

This can be easily mitigated by adding another condition for constraint_rectangle, namely radius is null.

However, I will not recommend doing so as it makes adding new type tedious.

For example, to add a new type triangle with one new column base, not only we will need to add a new constraint, but we also need to modify the existing constraints to ensure that their base is null.