Tension: Foreign key constraints are not supported on Tableland

[carsonfarmer]

Relationships between tables

Foreign keys are a central concept in SQL databases, and are a key component of the relational data model. Foreign Keys (FK) allow table creators to enforce data consistency. Usually they work with primary keys to connect two database tables. In a fully managed database deployment, this is an extremely powerful and pretty standard feature. However, in a permissionless database system such as Tableland, things can get complicated.

SQL foreign key constraints are used to enforce "exists" relationships between tables. For example, consider a database schema created using the following SQL commands:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER     -- Must map to an artist.artistid!
);

Without FK constraints, there is no way to enforce the above invariant. One solution is to add an SQL fFK constraint to the schema to enforce the relationship between the artist and track table. To do so, a FK definition may be added by modifying the declaration of the track table to the following (this text and all examples are pulled from the SQLite docs):

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

This is great, because now the constraint is enforced by the database. Attempting to insert a row into the track table that does not correspond to any row in the artist table will fail (super, that's what we want), as will attempting to delete a row from the artist table when there exist dependent rows in the track table (oh wait, this is getting complicated!). But what if I don't "own" or otherwise control the artist table, but I want to create a table that references it? Does my creating a FK constraint mean that table can't be burnt, or modified as easily in the future. That's not very permissionless between users!

So we need some way to control how constraint violations are handled. SQLite and other databases have a means to do this. But first, some terminology:

• The parent table is the table that a FK constraint refers to.
  • The parent table in the example in this section is the artist table.
• The child table is the table that a FK constraint is applied to and the table that contains the REFERENCES clause.
  • The example in this section uses the track table as the child table.
• The parent key is the column or set of columns in the parent table that the foreign key constraint refers to.
  • This is normally, but not always, the primary key of the parent table.
• The child key is the column or set of columns in the child table that are constrained by the foreign key constraint and which hold the REFERENCES clause.

Controlling behavior

The actual behavior of the FK constraints can be configured to avoid the above case where referencing a table imposes constraints on its operations. Foreign key ON DELETE and ON UPDATE clauses can be used to configure actions that take place when deleting rows from the parent table (ON DELETE), or modifying the parent key values of existing rows (ON UPDATE). A single FK constraint could in theory have different actions configured for ON DELETE and ON UPDATE (though as we will see, this is likely not going to be the case in practice).

The ON DELETE and ON UPDATE action associated with each FK in an SQLite database is one of "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE". If an action is not explicitly specified, it defaults to "NO ACTION". In practice though, the Tableland database is likely only ever going to consider a limited set of these action types:

• NO ACTION: Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken. This should really be the default. We don't want anything to happen to a child table if something happens with the parent key. This setting basically means, we'll enforce the constraint on the child, but not on the parent.
• RESTRICT: The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it. We can rule this one out completely, it is not useful in a permissionless system.
• SET NULL: If the configured action is "SET NULL", then when a parent key is deleted (for ON DELETE SET NULL) or modified (for ON UPDATE SET NULL), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values. This is probably fine, assuming there is not a NOT NULL constraint on the field in question. However, this means a change on a parent table will have downstream effects on a child table. This is perhaps unexpected behavior.
• SET DEFAULT: The "SET DEFAULT" actions are similar to "SET NULL", except that each of the child key columns is set to contain the column's default value instead of NULL. This one has some important caveats/invariants that would be too hard to maintain in practice, so we can probably rule out this option as well (for example, if there is no default value).
• CASCADE: A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an "ON UPDATE CASCADE" action, it means that the values stored in each dependent child key are modified to match the new parent key values. This one is tricky, because this is actually likely the type of behavior lots of projects would want... but it means that a change in a parent table could lead to a change in a child table, creating a direct and very real dependency.

Implications for dropping tables

If FK constraints are enabled , the DROP TABLE command (which Tableland does not support, but the following semantics might be relevant for #225) performs an implicit DELETE to remove all rows from the table before dropping it. The implicit DELETE does not cause any SQL triggers to fire, but may invoke FK actions or constraint violations. If an immediate FK constraint is violated, the DROP TABLE statement fails and the table is not dropped (for instance, if RESTRICT were used).

Tension

With the above information in mind, if we want to support FK constraints in Tableland, we need to come up with a proposal that will enable these constraints, without the dangerous behavior of locking tables with downstream or upstream references from affecting each other in unexpected ways.

Related Tension(s)

• Burnable Tables #225 - Burning tables may have consequences for tables with foreign key constraints