Wishlist

Inference from foreign key configuration for update/delete

We also discussed analysis of foreign key action configurations to also infer table and column properties without annotation. This may be a big enough task to split out as a separate issue? It also depends on informatics-isi-edu/ermrest#117

1. Foreign key "on update" actions describe how to treat mutation of referred keys

• "no action" or "restrict": the update will fail if it breaks a reference
• "cascade": update any references to reflect the changed key
• "set null": clear any references by setting them to null
• "set default": reset any references by setting them to defaults

2. Foreign key "on delete" actions describe how to treat deletion of referred rows

• "no action" or "restrict": the delete will fail if it breaks a reference
• "cascade": delete any referencing row(s) too
• "set null": clear any references by setting them to null
• "set default": reset any references by setting them to defaults

If the referencing table has default values set for referencing columns, "set default" and "set null" can behave differently. If they do not have default values set, they will both set references to null. If a non-null default is set, it can still cause an error unless the referenced table has a row using that default key.

So, we could use conservative rules to infer properties of a referenced table and its referenced keys:

1. Consider the table undeletable if deletes might cause an error
2. Consider the key column(s) immutable if updates might cause an error

The inbound references might cause an error if they create a conflict with the referencing table:

• Deletes or updates with "no action" or "restrict" can cause errors.
• Deletes or updates with "set default" can cause errors unless we analyze default expressions and table contents (which may be too complicated to worry about).
• Deletes or updates with "set null" can cause errors if the referring columns have "not null" constraints.

The change might propagates through other tables and cause a conflict in a more remotely connected table:

• Deletes with "cascade" can cause deletes in the referencing table. Recursively analyze the effect of this indirect delete including propagation across inbound references on that table. This has to consider all inbound references, so the propagation can continue throughout the graph of connected tables.
• Deletes with "set null" or "set default"; and updates with "cascade", "set null", or "set default" can cause updates in the referencing table. Recursively analyze the effect of this indirect update including propagation across inbound references to the updated columns on that table. This only considers inbound references which overlap the foreign key columns which would be indirectly affected, so the propagation can extinguish in models without overlapping key and foreign key columns.

After analyzing the indirect effects and their potential to raise errors, we can then conclude whether the table should be considered undeletable or the referenced key columns immutable.