Proposal: Allow foreign key constraints with limited actions

[carsonfarmer]

Allow foreign keys

Based on #243 Tableland should support foreign key constraints on child tables, such that these constraints do not impose any constraints on operations on the parent (or upstream reference) table. Ideally, this proposal will also ensure that there are no unexpected downstream effects on child tables due to changes on the parent table. To this effect, the following changes to the Tableland spec are proposed, requiring updates to the Tableland SQL parser, and likely some additional runtime checks on the validator.

Limit actions

The proposal is to introduce a subset of the standard SQL foreign key constraint clause to the Tableland SQL specification. Namely, to enable the following syntax within the table-constrain clause of the CREATE TABLE statement:

FOREIGN KEY(column-name, ...) 
  REFERENCES  foreign-table(column-name, ..)
  [ON DELETE action]
  [ON UPDATE action]

Where column-name can be any table column name, foreign-table is any valid table name (including tables create via other chains), and action can be one of:

• SET NULL
• NO ACTION

I am also accepting arguments in favor of including:

• SET DEFAULT
• CASCADE

Though there is no way that we can support RESTRICT.

The default behavior when either of ON DELETE or ON UPDATE are not provided, would be NO ACTION.

Related tension

• Tension: Foreign key constraints are not supported on Tableland #243

[jsign]

I’ve some thoughts about this proposal. I’ll separate them with markers!

---

I might be missing something, but NO ACTION I think, isn’t an option. I know others on the team have suggested this, but I’m not sure how it would work.

I think the best way for me to summarize my point is by providing the following SQLite3 example:

sqlite> pragma foreign_keys=on;
// create tables and insert some values.
// note that the default is NO ACTION.
sqlite> create table parent (id integer primary key);
sqlite> create table child (id integer references parent(id));
sqlite> insert into parent values (1);
sqlite> insert into child values (1);

// try 'delete'
sqlite> delete from parent;
Runtime error: FOREIGN KEY constraint failed (19)
// ^ parent isn't allowed to delete because of child.

// try 'update'
sqlite> update parent set id=2;
Runtime error: FOREIGN KEY constraint failed (19)
// ^ similar result

I think this example is quite self-explanatory.

Maybe I'm missing something here, LMK!

---

Agree with you that RESTRICT and CASCADE can’t be options.

I’d say SET NULL might work at the SQL layer, but I’ll dig deeper.

For SET NULL to work, the column should be nullable. If configured with NOT NULL, it will block the parent from doing UPDATE or DELETEs, so it’s a no-go.

This then means that all FK columns must be NULL-able. So the application logic should always consider a valid situation where the value is NULL, and no parent data can be fetched. Saying it differently, the child application should be fine at any moment for the FK to be null since a parent UPDATE or DELETE would set it to NULL.

Any SELECT that did a JOIN in that column should expect at any point without coordination to stop joining and “disappear” from that SELECT result. Also, accepting to have that "orphaned" table row (whatever that means for the app).

Is this way what we wanted to solve originally in the tension?

---

From a birds-eye, I think any FK option can create a big risk for Tables. For example, if 1000 tables reference some popular table, and the parent table owner decides is a good idea (or by mistake) to execute a DELETE or UPDATE, it will indirectly affect the 1000 tables.

You could say: “Well, those 1000 tables should already know where they were getting into,” but I’m not entirely sure that’s a friendly answer if we enabled a potentially risky feature.

I’m a bit afraid it could break the ecosystem apps badly. That’s why I’m suspicious about magically-allowing cascading options such as SET NULL. Usually, an FK CONSTRAINT violation is what prevents things from breaking, SET NULL kind of silently works around the situation and might give you a surprise later (if users don’t correctly understand the consequences of using it).

This is debatable and subjective; just leaving it as a general comment.

---

Regarding:

foreign-table is any valid table name (including tables created via other chains)

I think we need to also dig deeper into what that means:

1. Having FK to other chains tables leads to non-deterministic execution. When a write-query is executed in chain A, there are no guarantees on the Tables state of chain B. If Validator-1 executes the write-query, it might have chain B in a different height than Validator-2 thus, that’s a non-deterministic action.
2. This cross-chain linkage exploits a current assumption that all validators as syncing all chains, which I think it’s something we should look at carefully.

[sanderpick]

Thanks for digging into this.

Usually, an FK CONSTRAINT violation is what prevents things from breaking

This is a good point. If we add FK support, it would have to be implemented in a way that sorta defeats its own purpose.

Of course, users can create relations between tables in the usual way (ID references, etc.). I could have sworn I ran into a case where I really wanted FK constraints in the example-voter application, but looking back, I don't see it. There were other limitations I ran into, but I'll document those in an issue.