Update / Delete Cascade rules

Update and Delete rules for relationships and foreign key constraints are supported on the following databases: SQL Server, Firebird, Postgresql, MS Access (database first only) and Oracle (Delete rules only). Most people know Update and Delete rules as CASCADE DELETE or CASCADE UPDATE statements.

Update / Delete rules aren't used in the entity model, however they're editable through the entity relationships in the model. See below.

Database First

The Update and Delete rules are read for the supported databases for every foreign key constraint found in the schema data read from the database. The Catalog Explorer shows the Update and Delete rules below every foreign key constraint and right-clicking them allows the user to change the values for these rules using the context menu. When a relationship is reverse engineered from a foreign key constraint, it inherits the Update / Delete rule value of said foreign key constraint.

Model First

Every normal entity relationship (1:1, m:1 or 1:n) has an Update rule and a Delete rule value. A new normal entity relationship obtains the initial values for its update rule and delete rule from the project settings resp. Default relationship delete rule action and Default relationship update rule action.

When the relationship is a model-only relationship, the Update rule and Delete rule have no effect as they're currently not used for other purposes than creating the Update / Delete rule on the backing foreign key constraint(s).

When the relational model data is synchronized with the Abstract Entity Model through Syncing the Relational Model Data, the Update / Delete rule values for a given non-model-only normal relationship are set onto its backing foreign key constraint(s). These values are then emitted as a Foreign Key constraint change in an exported DDL SQL Update script.

Combining database first and model first

As the designer allows you to use both together, the model-first oriented functionality can be used to alter an existing foreign key constraint's update and / or delete rule which was previously read from the database using the database-first functionality, e.g. by Syncing the Relational Model Data. It's required to set the sync source to 'Mixed' or 'Model' before performing the Sync Task.

Supported update / delete rules per database

LLBLGen Pro supports update / delete rules on the databases specified below with the rule values mentioned. The databases mentioned below are the ones from which meta-data was retrievable related to update/delete rules. Databases like MySQL do support update/delete rules however MySQL foreign key meta-data doesn't specify the update/delete rule values set. If a rule value isn't supported, the DDL SQL script will fall back to NoAction.

MS Access

MS Access' delete rules are only supported in the database-first scenario as there's no model-first scenario for MS Access. Rule values supported: NoAction and Cascade.

Firebird

Rule values supported: Cascade, SetDefault, SetNull and NoAction

Oracle

Oracle only supports Delete rules on foreign key definitions, no update rules. If update rules are required, they have to be implemented through triggers. Rule values supported: Cascade, SetNull and NoAction

PostgreSql

Rule values supported: Cascade, SetDefault, SetNull, Restrict and NoAction.

SQL Server

Rule values supported: Cascade, SetDefault, SetNull and NoAction. Remember that SQL Server 2000 only supports Cascade and NoAction.

Google Cloud Spanner

Google Cloud Spanner supports Delete rules, on interleaved tables (through a pk-pk relationship). Rule values supported: Cascade, NoAction.

Inheritance edge backing Foreign Key constraints

Inheritance edges for an inheritance hierarchy of type TargetPerEntity always have for both rules the value Cascade.