Foreign keys have a very specific implementation in libSQL/SQLite. In order for them to be effective, each db connection needs to activate PRAGMA foreign_keys=on, otherwise they are not honored. Moreover, as the official docs say:
It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode)
That's problematic with sqld's server model, because we do not generally guarantee that user sessions are always based on a single database connection, and the matter becomes even more problematic with replica write delegation.
If we don't do anything, users can observe anomalies, e.g. foreign key constraints or cascading deletes not working, despite them sending PRAGMA foreign_keys=on in the same database session.
We may need a mechanism that activates automatic PRAGMA foreign_keys=on server-side, e.g. for all connections coming from a particular user. Since we already parse statements client-side, we can even build the mechanism on top of PRAGMA foreign_keys=on, and send a specific request to the server if a user sends it. Or, we can make it even more coarse-grained and allow creating foreign-key-enabled databases that always start with foreign_keys=on -- under the assumption that foreign keys should generally either be honored at all times, or not at all.
Foreign keys have a very specific implementation in libSQL/SQLite. In order for them to be effective, each db connection needs to activate
PRAGMA foreign_keys=on, otherwise they are not honored. Moreover, as the official docs say:That's problematic with sqld's server model, because we do not generally guarantee that user sessions are always based on a single database connection, and the matter becomes even more problematic with replica write delegation.
If we don't do anything, users can observe anomalies, e.g. foreign key constraints or cascading deletes not working, despite them sending
PRAGMA foreign_keys=onin the same database session.We may need a mechanism that activates automatic
PRAGMA foreign_keys=onserver-side, e.g. for all connections coming from a particular user. Since we already parse statements client-side, we can even build the mechanism on top ofPRAGMA foreign_keys=on, and send a specific request to the server if a user sends it. Or, we can make it even more coarse-grained and allow creating foreign-key-enabled databases that always start withforeign_keys=on-- under the assumption that foreign keys should generally either be honored at all times, or not at all.