Do “ON UPDATE CASCADE” and “ON DELETE CASCADE” functionality allowed along with a FOREIGN KEY in a table?
We support the syntax for compatibility, but we actually don't enforce those operations.
Although referential integrity is not enforced by NuoDB, meaning a value inserted into the referencing columns(s) of this new table is not currently matched against the values of the referenced table and referenced column(s), a trigger can be defined that will enforce this integrity constraint.
There are two ways to accomplish it:
1) If you want to do an "ON DELETE CASCADE", write an ON DELETE trigger that will search a specific column value in a specific table, and delete those rows, this is an ad-hoc trigger that never changes until you modify it. The same logic for "ON UPDATE CASCADE".
2) create a generic trigger that for each operation will scan the table definition from the system tables and infer which are the referential integrity operations defined on the table, and run them, this is a single trigger that dynamically adapts to the definition of the table.
More information about foreign key can be found here.